Union identical tables in one view

[elauri]

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}

[mail2saion]

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

[elauri]

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

[elauri]

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
...