0

Hello all,

my first post.

I have identical tables in my db. Is there a way to create a view with dynamic query to combine/union all the identical tables into one view?

dbo.table.x-1
dbo.table.x-2
dbo.table.x-3
dbo.table.x-{n}

2
Contributors
3
Replies
4
Views
8 Years
Discussion Span
Last Post by elauri
0

You can create a view dynamically in the following way:

Declare @SQL nVarChar(1000)

Select @SQL = 'Create View Auths AS (SELECT au_id, au_lname, au_fname FROM Authors)'

Execute pubs.dbo.sp_executesql @sql
0

Thanks for your answer. Sorry I give a better explanation.

Currently I have solved the union problem by creating a cursor and creating a new table to insert all other tables.

DECLARE CURSOR_TABLES CURSOR FOR
       SELECT name
       FROM sys.tables
       WHERE name LIKE '%TABLE-%

Here I created the cursor with all the tables I want to combine. The tables have identical structure.

OPEN CURSOR_TABLES 
FETCH NEXT FROM CURSOR_TABLES INTO @TableName
WHILE @@FETCH_STATUS <> -1
  BEGIN
     SET @SQLInsertCmd = 'INSERT INTO dbo.TABLE SELECT table_names FROM ' + @TableName
     EXEC (@SQLInsertCmd)

     FETCH NEXT FROM CURSOR_TABLES INTO @TableName
  END
0

Actually I found out I can use my Cursor to create a sql statement

CREATE VIEW ViewTables AS
SELECT * FROM table1
UNION ALL
SELECT * FROM table2
...
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.