Trying to avoid looping for INSERT FROM SELECT

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)

Trying to avoid looping for INSERT FROM SELECT

Author

Topic

nlouis
Starting Member

2 Posts

Posted - 2011-06-08 : 17:41:30

Don't know T-SQL well and I'm probably stuck with having to use a loop, but just in case, I'll ask here...

If it were just an INSERT FROM SELECT, I'd be done:

insert into table1(col1, col2)
select col1, col2 from table2

but I need to take the identity value created during the insert into Table1 and insert it into a table3.

Am I stuck having to do the initial select from Table2, then looping to do the all the inserts into Table1, getting the IDs with scope_identy(), then second insert into Table3 within the loop?

I've got 600K+ records to this to. Would a trigger on insert along with a standard INSERT FROM SELECT run faster than the loop?

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-06-08 : 19:21:56

many ways to skin the cat

this is what I do. 600K..might need a special loop to do it in batches of 10,000 or something

1. create a table variable or a temp table (depending on how many rows you are inserting)
declare @newrows table(tableid int)
2. dump all newly inserted row identities into table variable during insert
insert into table1(col1, col2)
OUTPUT table1.id INTO @newrows
select col1, col2 from table2

3. SELECT * FROM @newrows would give you all the new ids

If you don't have the passion to help people, you have no passion

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-08 : 20:20:04

If you are on SQL 2005 or higher (and I assume you are, since you posted the question in SQL 2008 forum), look up OUTPUT clause. That will let you insert data into one table, and then insert the "results" of the insert into another table.

If you have difficulties with syntax, post your table DDL and I am sure people on this forum would be able to help you.

http://msdn.microsoft.com/en-us/library/ms177564.aspx

nlouis
Starting Member

2 Posts

Posted - 2011-06-09 : 08:29:30

I was unaware of the OUTPUT clause. That will be very helpful in my eventual solution. Thank you very much, yosiasz and sunitabeck.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2011-06-09 : 08:52:39

or perhaps OUTPUT directly into table 3 and skip the temp table altogether.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources