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

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

Featured Post

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied?
You can use SQL Server Initialize from Backup…

Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment.
To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…