Solved

Insert data from multiple tables into one temp table

Posted on 2012-12-27
8
1,487 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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
 
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:
Scott Pletcher 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: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

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

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

776 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