Insert data from multiple tables into one temp table

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
Apostle07Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Scott PletcherConnect With a Mentor Senior DBACommented:
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
 
Saurabh BhadauriaCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Saurabh BhadauriaCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I see ... still it will not be 1 recordset, the UNION ALL is missing.
0
 
Saurabh BhadauriaCommented:
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
 
Apostle07Author Commented:
Thank you for the help everyone.
0
 
Scott PletcherSenior DBACommented:
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
All Courses

From novice to tech pro — start learning today.