Insert set, get identity values

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 2012 Forums

Transact-SQL (2012)

Insert set, get identity values

Author

Topic

Luuk123
Yak Posting Veteran

52 Posts

Posted - 2013-10-16 : 08:24:17

Hi all,

I have a situation where I'm inserting a set of rows from table one into table tow where the primary key column of table tow is an identity. The inserted rows are grouped rows from table one. There are no referencing columns between table one and two.

Is there a way to get all the inserted identity values? If yes, is there a way to get all the identity values and reference them to the inserted rows?

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-16 : 08:28:29

yes. use OUTPUT clause and capture details to a table variable

code will look like


DECLARE @INSERTED_VALUES table
(
IDVal int,
COl1 ...,
Col2 ..,
..
)

INSERT TableTwo(Col1,Col2,..)
OUTPUT INSERTED.ID,INSERTED.COl1,INSERTED.Col2... INTO @INSERTED_VALUES
SELECT Col1,Col2,..
FROM TableOne


SELECT *
FROM @INSERTED_VALUES

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Luuk123
Yak Posting Veteran

52 Posts

Posted - 2013-10-16 : 08:47:18

Thanks!

I thought it was not possible to output the identityvalues, but it works great!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-16 : 09:00:14

Nope.. you can
And it can be used with INSERT,UPDATE,DELETE or even MERGE statements

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources