Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Insert data from multiple tables into one temp table

Posted on 2012-12-27
8
Medium Priority
?
1,827 Views
Last Modified: 2013-01-08
Hi,

I'm hoping somebody can help me out.  

I'm looking to have data from multiple tables inserted into one temporary table.  I was hoping to do this with a dynamic sql query.  So far I've been able to come up with the below which basically returns results for each table in it own resultset pane.  Again, I'm looking to get the data into one temp table.

Any help would be appreciated.

Thank you.



DECLARE @Table_Name sysname,

@DynamicSQL nvarchar(4000)

DECLARE @TableCursor CURSOR
SET @TableCursor = CURSOR LOCAL FORWARD_ONLY DYNAMIC FOR

SELECT DISTINCT TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE RIGHT(TABLE_NAME,4) ='CLID'
 

OPEN @TableCursor

FETCH NEXT FROM @TableCursor INTO @Table_Name

WHILE @@FETCH_STATUS = 0

BEGIN

SET @DynamicSQL = N'SELECT * FROM ' + @Table_Name
EXECUTE sp_executesql @DynamicSQL

FETCH NEXT FROM @TableCursor INTO @Table_Name

END

CLOSE @TableCursor

DEALLOCATE @TableCursor
0
Comment
Question by:Apostle07
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 12

Expert Comment

by:Saurabh Bhadauria
ID: 38726182
No Need to use cursor for this.... below code will give result for each  table which is staring with 'CLID'

Declare @sql nvarchar(max)
select @sql= ''
select  @sql = @sql+b.col1 + a.table_name + CHAR(13) from
(SELECT DISTINCT TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE RIGHT(TABLE_NAME,4) ='CLID' ) a
Cross join  (select 'select * from ' col1) b

exec (@sql)

Open in new window

To insert a data into a single table from so many tables , you need to make sure all tables should have same schema structure..

if your tables has same schema then add a temp table  in above code with your column sets.. like this...

Declare @sql nvarchar(max)

Create table #temptable 
(
id int,
Column1 nvarchar(20),
Column2 nvarchar(20),
Column1 nvarchar(20),
Column1 nvarchar(20),
...............
..................
..................
--Your column sets

)

select @sql= ''

select  @sql = @sql+b.col1 + a.table_name + CHAR(13) from
(SELECT DISTINCT TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE RIGHT(TABLE_NAME,4) ='CLID' ) a
Cross join  (select 'select * from ' col1) b

insert into #temptable
exec (@sql)

Open in new window


let me know if you have any concern....
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38726377
CROSS JOIN will create a matrix, and will not UNION ALL the results...

to get all the results as 1 resultset, assuming that all the tables have the same layout ..

DECLARE @Table_Name sysname,
@DynamicSQL nvarchar(4000), @union varchar(20)

SET @union = ''
DECLARE @TableCursor CURSOR
SET @TableCursor = CURSOR LOCAL FORWARD_ONLY DYNAMIC FOR

SELECT DISTINCT TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE RIGHT(TABLE_NAME,4) ='CLID'
 

OPEN @TableCursor
FETCH NEXT FROM @TableCursor INTO @Table_Name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @DynamicSQL = @DynamicSQL + @union +  N'SELECT * FROM ' + @Table_Name
SET @union = ' UNION ALL '
FETCH NEXT FROM @TableCursor INTO @Table_Name

END
CLOSE @TableCursor
DEALLOCATE @TableCursor 

EXECUTE sp_executesql @DynamicSQL

Open in new window

0
 
LVL 12

Expert Comment

by:Saurabh Bhadauria
ID: 38726503
Hi Angle,

Run below query ... I am sure your doubt will be clear...  

Declare @sql nvarchar(max)
select @sql= ''
select  @sql = @sql+b.col1 + a.table_name + CHAR(13) from
(SELECT DISTINCT TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
 ) a
Cross join  (select 'select * from ' col1) b
print @sql 
exec (@sql)

Open in new window


Below code will generate  dynamic Sql  like this..

'select * from table1
select * from table2
select * from table3
..............................

And by executing this dynamic Sql  through exec  we can insert the result from all tables in a temp table ..


Thanks,
Saurabh
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38726528
I see ... still it will not be 1 recordset, the UNION ALL is missing.
0
 
LVL 12

Expert Comment

by:Saurabh Bhadauria
ID: 38726552
yap.. It doesn't have UNION ALL but.....

when you will execute a dynamic sql   it will execute each individual select query and insert data in temp table from all record sets ...

insert into #tempTable
exec(@sql)    

#tempTable will have data from each records set  so no need of union all...


But still we can tweak our sql to put UNION ALL inside it.  like this...

Declare @sql nvarchar(max)
select @sql= ''
select  @sql = @sql +  case when @sql > '' then ' UNION ALL ' + CHAR(13) else ' ' end   +b.col1 + a.table_name  from
(SELECT DISTINCT TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
 ) a
Cross join  (select 'select * from ' col1) b

print @sql
--exec (@sql)

Open in new window

0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 38727609
I suggest NOT using UNION ALL.  UNION ALL forces a vastly larger batch and means one error, such as invalid data, causes the whole load to fail, quite possibly after you've waited HOURS for the earlier SELECTs of a massive UNION ALL to run.

I've used a global temp table to allow the creation of the temp table to be dynamic.  That means this code must be run 1-up (sync), never simultaneous (async).


DECLARE @Table_Name sysname
DECLARE @DynamicSQL nvarchar(4000)
DECLARE @First_Table bit

DECLARE @TableCursor CURSOR
SET @TableCursor = CURSOR FAST_FORWARD FOR
SELECT name
FROM sys.tables
WHERE RIGHT(name,4) = N'CLID'

SET @First_Table = 1
 
OPEN @TableCursor

FETCH NEXT FROM @TableCursor INTO @Table_Name

WHILE @@FETCH_STATUS = 0

BEGIN

SET @DynamicSQL = CASE WHEN @First_Table = 1 THEN N'' ELSE
    N'INSERT INTO ##CLID ' END +
    N'SELECT * ' + CASE WHEN @First_Table = 1 THEN N'INTO ##CLID ' ELSE N'' END +
    N'FROM [' + @Table_Name + ']'
PRINT @DynamicSQL
--PRINT 'Loading  table: ' + @Table_Name
EXECUTE sp_executesql @DynamicSQL

SET @First_Table = 0

FETCH NEXT FROM @TableCursor INTO @Table_Name

END --WHILE

CLOSE @TableCursor
DEALLOCATE @TableCursor
0
 

Author Closing Comment

by:Apostle07
ID: 38757037
Thank you for the help everyone.
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 38757202
If you really need to do multiple runs of this at the same time (async), you could generate a unique global table name within the script, although that would force you to make the initial create of the global temp table dynamic SQL as well.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

618 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