Solved

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

Posted on 2014-12-22
5
83 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
Comment Utility
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
Comment Utility
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
Comment Utility
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 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
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.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

762 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now