MS SQL

MS SQL RSS

Union identical tables in one view

Please support our MS SQL advertiser: Intel Parallel Studio Home

Thread Solved

•

Join Date: Nov 2009

Posts: 6

Reputation: elauri is an unknown quantity at this point

Solved Threads: 0

elauri

Offline

Newbie Poster

Union identical tables in one view

21 Days Ago

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}

•

Join Date: Apr 2009

Posts: 217

Reputation: mail2saion is an unknown quantity at this point

Solved Threads: 31

mail2saion mail2saion is offline

Offline

Posting Whiz in Training

21 Days Ago

You can create a view dynamically in the following way:

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
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
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

MARK AS SOLVED if its help you.

REGARDS
MCTS - Shawpnendu bikash maloroy
http://shawpnendu.blogspot.com

•

Join Date: Nov 2009

Posts: 6

Reputation: elauri is an unknown quantity at this point

Solved Threads: 0

elauri

Offline

Newbie Poster

20 Days Ago

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.

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
DECLARE CURSOR_TABLES CURSOR FOR
       SELECT name
       FROM sys.TABLES
       WHERE name LIKE '%TABLE-%
 
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.

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
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
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

•

Join Date: Nov 2009

Posts: 6

Reputation: elauri is an unknown quantity at this point

Solved Threads: 0

elauri

Offline

Newbie Poster

20 Days Ago

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

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
CREATE VIEW ViewTables AS
SELECT * FROM table1
UNION ALL
SELECT * FROM table2
...
CREATE VIEW ViewTables AS
SELECT * FROM table1
UNION ALL
SELECT * FROM table2
...