asked on 12/22/2014

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.

Microsoft SQL Server 2008

Last Comment

AD1080

12/23/2014

Jesus Rodriguez

12/22/2014

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

AD1080

12/22/2014

ASKER

Hi Jesus,

Very nice. How would I pass the @QRY variable into the CREATE VIEW statement?

ASKER CERTIFIED SOLUTION

Jesus Rodriguez

12/22/2014

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

Vitor Montalvão

12/23/2014

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?

AD1080

12/23/2014

ASKER

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.