Create View of Client Application Generated Tables, Updated Regularly on Set Schedule

Hi,

I need some assistance on how to create a view that would be updated on a set schedule.  (probably hourly.)  

The client app I am working with generates temporary tables daily that are named with the prefix "M001948"

The view would something to the effect of:

CREATE VIEW V1 AS 

SELECT * FROM M0019480001

UNION

SELECT * FROM M0019480002

UNION 

SELECT * FROM M0019480003

Open in new window



I am not sure where to start, as I don't know the table names ahead of time.    I know I can get a result set of the current tables with something like.
         
       
 SELECT * FROM SYS.TABLES 
         WHERE (
         NAME LIKE 'M001948%' 

Open in new window


Thanks in advance for your assistance.
AD1080Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jesus RodriguezIT ManagerCommented:
You can get the whole query created like this

	
        DECLARE @TABLES VARCHAR(15)
	DECLARE @QRY AS VARCHAR(MAX)

	DECLARE TBL CURSOR FOR 
				 SELECT NAME FROM SYS.TABLES 
				 WHERE (
				 NAME LIKE 'M001948%')
	OPEN TBL
	FETCH FROM TBL INTO @TABLES
	WHILE @@FETCH_STATUS<>0
	 BEGIN
	  SET @QRY= @QRY + 'SELECT * FROM '+@TABLES +' UNION '
	 FETCH NEXT FROM TBL INTO @TABLES
	END
	SET @QRY=LEFT(@QRY,LEN(@QRY)-7)
	CLOSE TBL
	DEALLOCATE TBL

Open in new window

0
AD1080Author Commented:
Hi Jesus,

Very nice.  How would I pass the @QRY variable into the CREATE VIEW statement?
0
Jesus RodriguezIT ManagerCommented:
Ok, before continue please, read this 2 post

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/af8b0c2d-8117-42e3-a701-05035644f73d/is-it-possible-to-create-a-view-within-a-stored-procedure?forum=sqlgetstarted
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/56ee5855-c7e5-49d1-a0e3-3dabf77a7872/creating-a-view-inside-a-stored-procedure?forum=transactsql

 They give you few suggestion on it but try to DO  NOT CREATE A VIEW FROM THE RESULT OF RUNNING AN STORE PROCEDURE. You can run your query and inserting into a temp table always and then create the view from this Table but is not recommendable to recreate a view again and again

 You can do something like this

-  Create an store procedure with this code

	DECLARE @TABLES VARCHAR(15)
	DECLARE @QRY AS VARCHAR(MAX)

	DECLARE TBL CURSOR FOR 
				 SELECT NAME FROM SYS.TABLES 
				 WHERE (
				 NAME LIKE 'M001948%')
	OPEN TBL
	FETCH FROM TBL INTO @TABLES
	WHILE @@FETCH_STATUS<>0
	 BEGIN
	  SET @QRY= @QRY + 'SELECT * FROM '+@TABLES +' UNION '
	 FETCH NEXT FROM TBL INTO @TABLES
	END
        IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'TMP') 
           BEGIN
                  DROP TABLE TMP
           END 
	SET @QRY='INSERT INTO TMP ' + LEFT(@QRY,LEN(@QRY)-7)
        EXEC (@QRY)
	CLOSE TBL
	DEALLOCATE TBL

Open in new window


- THEN CREATE A VIEW FROM THE TMP TABLE THAT WAS RE-CREATED ON THE DATABASE
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
There's no purpose to create a view that will always work with a different set of tables. Why don't you use a stored procedure instead?
0
AD1080Author Commented:
Thanks Jesus.  I see what you were saying now.  

Yes, Vitor, this was a misunderstanding on my part, and a stored procedure may be more appropriate.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.