Solved

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

Posted on 2014-12-22
5
89 Views
Last Modified: 2014-12-23
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.
0
Comment
Question by:AD1080
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 13

Expert Comment

by:Jesus Rodriguez
ID: 40513928
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
 

Author Comment

by:AD1080
ID: 40513997
Hi Jesus,

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

Accepted Solution

by:
Jesus Rodriguez earned 500 total points
ID: 40514149
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
 
LVL 49

Expert Comment

by:Vitor Montalvão
ID: 40514615
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
 

Author Comment

by:AD1080
ID: 40515056
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

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Count with a subquery showing details 10 57
SSRS 2012 r2 - Parm Drop Down has Date/Time 12 44
Help Required 3 117
How to debug a store procedure in MS SQL 2008? 3 32
I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question