CODEWITHSUNDEEP
sql-server-2008
Jean-Francois
Jean-Francois

Reputation: 1949

How to get the primary key values after insert select statement

I need to create a SQL script with many step in it.

First of all, I need to insert data into a Parent Table.

How can I Get the list of primary key value

Here is an example of what I'm trying to perform.

MyParentTable MyParentID PK col1, col2, col3

--INSERT VALUE INTO THE PARENT TABLE

insert into MyParentTable(col1,col2,col3)
select SDATA1,SDATA2,SDATA3
from ExampleTables

I Would like to get the list of my newly entries.

How to do that?

IMPORTANT NOTE : Consider that MyParentTable can alreaydy contains data.

Upvotes: 1

Views: 4023

Answers (1)

anon
anon

Reputation: 

insert into dbo.MyParentTable(col1,col2,col3)
output inserted.identity_column_name
select SDATA1,SDATA2,SDATA3
from dbo.ExampleTables;

If there are foreign keys involved, you may have to use a @table variable for temporary holding.

DECLARE @t TABLE(id INT);

insert into dbo.MyParentTable(col1,col2,col3)
output inserted.identity_column_name INTO @t
select SDATA1,SDATA2,SDATA3
from dbo.ExampleTables;

SELECT id FROM @t;

Upvotes: 2

Related Questions