insert from various tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

insert from various tables

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2011-11-08 : 06:32:03

Hi,
There are about 20 table names in tblMain.
for each tablename I have to do the same insert into one table i.e. tblAll as these tables have the same field names.

instead of doing the following sql, what is the best way to do it?
Thanks

insert into tblAll
select name, surname, address1 from "firstTable"

insert into tblAll
select name, surname, address1 from "secondTable"

insert into tblAll
select name, surname, address1 from "thirdTable"
.
.
.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-08 : 06:37:23

create table @a (name varchar(200))
insert @a select 'firstTable'
insert @a select 'secondTable'
insert @a select 'thirdTable'

declare @tbl varchar(200)
select @tbl = ''
while @tbl < (select MAX(name) from @a)
begin
select @tbl = MIN(name) from @a where name > @tbl

select @sql = 'insert tblAll select name, surname, address1 from [' + @tbl + ']'
exec (@sql)
end

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2011-11-08 : 06:55:19

Isn't a cursor easier for this task?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-08 : 07:01:14

Never written one but I would say no.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-08 : 07:01:34

quote:
Originally posted by arkiboys

Isn't a cursor easier for this task?

cursor will also processing in same way ie row by row

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2011-11-08 : 09:20:12

solved. thanks guys.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-08 : 09:26:59

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources