Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2014-12-22
5
Medium Priority
?
93 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
  • 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 2000 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 52

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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
In this article I will describe the Copy Database Wizard 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.
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

772 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