?
Solved

Insert data from multiple tables into one temp table

Posted on 2012-12-27
8
Medium Priority
?
1,725 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
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 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 69

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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 set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

800 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