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.
| Author |
Topic |
|
razeena
Yak Posting Veteran
54 Posts |
 Posted - 2011-09-15 : 09:15:46
|
| Hi ,I am taking 100 customerids from a customer table to a table variable and joining this with anothertable say customerorder inside a while loop. The customerorder may contain more than one order for each customer.While joining the custids in the temporary tablewith the customerorder, suppose I got 20 records in the first batch of insertion and 50 recordsin the second batch whiledoing the 'insert into ... select'.How can I retrieve the records inserted in each batch? |
|
|
Kristen
Test
22859 Posts |
Posted - 2011-09-15 : 09:22:19
|
| INSERT INTO ... SELECT @BatchNo, OtherCol1, OtherCol2, ...SELECT @BatchNo = @BatchNo + 1?? |
 |
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2011-09-15 : 10:51:14
|
| Utilize the OUTPUT clause during your inserts.http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspxHow to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxFor ultra basic questions, follow these links.http://www.sql-tutorial.net/ http://www.firstsql.com/tutor.htm http://www.w3schools.com/sql/default.asp |
|
|
|
ashishashish
Constraint Violating Yak Guru
408 Posts |
Posted - 2011-09-16 : 02:03:06
|
| HI Razeena ... as Don suggested you can use output clause during your inserts like this..INSERT INTO YourTable output inserted.yourvalue1, inserted.yourvalue2, inserted.yourvalue3,... so onINTO InsertedTable --If you want to insert the records is some kind of audit table than use this else commit it.SELECT yourvalue1,yourvalue2,yourvalue3...so oniF theRe iS a wAy iN tHen theRe iS a wAy oUt.. |
|
|
|
|
|
|
Identify records inserted in each batch