Solved

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

Posted on 2014-12-22
5
91 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 51

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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

627 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