Solved

Insert data from multiple tables into one temp table

Posted on 2012-12-27
8
1,446 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
  • 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 142

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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38726528
I see ... still it will not be 1 recordset, the UNION ALL is missing.
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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 69

Accepted Solution

by:
ScottPletcher earned 500 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 69

Expert Comment

by:ScottPletcher
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

932 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now