Is there a way to retrieve all the keys of the newly inserted records when using an INSERT INTO ... SELECT FROM query?
Good to know. I don't have to use cursors anymore :)
ps
2009-12-07 23:47:10
A:
Some databases support the INSERT INTO ... SELECT ... RETURNING ... syntax. Since you are using TSQL, I believe the syntax for that is:
INSERT INTO table (fields...)
OUTPUT outputfields...
SELECT ...
There's a PDF on the issue: Returning.pdf
Kenaniah
2009-12-07 23:45:28
+5
A:
DECLARE @MyVar TABLE ( ID int )
INSERT INTO dbo.TargetTable
OUTPUT INSERTED.ID INTO @MyVar
SELECT * FROM dbo.SourceTable
SELECT * FROM @MyVar
Damir Sudarevic
2009-12-07 23:51:24
I think I got it, I'd just modify the table structure and then change the output line to something like this, right? OUTPUT dbo.SourceTable.ID, INSERTED.ID INTO @MyVar
adam0101
2009-12-08 00:18:58
Yes, just add a column to @MyVar and one more INSERTED.Something
Damir Sudarevic
2009-12-08 01:58:40