Help with T-SQL script: Insert record, then use identity of that insert on another statement?

Question

Just as a preface, I'm not very knowledgeable on T-SQL syntax.

I'd like to create a simple SQL script that will make 3 insert statements.

Insert A
Insert B
Insert C

Insert A statement's identity or "ID" would be needed in insert B statement. And both the identities Inserts A & B would be needed in Insert C statement.

Pseudo code would look something like:

INSERT INTO tableA
VALUES ('blah', 'blah')

INSERT INTO tableB
VALUES (IDENTITY_FROM_A_INSERT, 'foo')

INSERT INTO tableC
VALUES (IDENTITY_FROM_A_INSERT, IDENTITY_FROM_B_INSERT)

How would I go about writing this script?

Related question: http://stackoverflow.com/questions/42648/best-way-to-get-identity-of-inserted-row — Oded, Feb 28 '11 at 21:56

score 19 · Accepted Answer · answered Feb 28 '11 at 21:53

19

Use SCOPE_IDENTITY() after each insert in order to get the identity of the inserted row (in the current session).

I have used two variables to capture the two identities and then insert them into the third table:

DECLARE @Id1 INT
DECLARE @Id2 INT

INSERT INTO tableA VALUES ('blah', 'blah')

SET @Id1 = SELECT SCOPE_IDENTITY()

INSERT INTO tableB VALUES (IDENTITY_FROM_A_INSERT, 'foo')

SET @Id2 = SELECT SCOPE_IDENTITY()

INSERT INTO tableC VALUES (@Id1, @Id2)

answered Feb 28 '11 at 21:53

Oded

489,969
99
883
1,009

1

Maybe I'm nuts, but isn't the syntax actually `select @Id1 = scope_identity()`? – Dan Esparza Dec 30 '13 at 16:58
1

@DanEsparza - that is also valid syntax. – Oded Dec 30 '13 at 17:04
1

Or use the OUTPUT variable if you will be inserting multiple records in the first insert. – HLGEM Apr 18 '18 at 17:39

canon · Answer 2 · 2018-04-23T14:06:43.840

scope_identity() is perfect for integer identifiers on single-record insertions (+1 to the other answer btw). However, if you find yourself using a guid/uniqueidentifier (newsequentialid(), newid(), etc) or inserting multiple records at once, you'll need something a little different:

declare @id uniqueidentifier;
-- Table variable for storing your newly inserted identifiers:
declare @NewlyInsertedIds table 
(
   [Id] uniqueidentifier
);

insert [MyTable]
(
   [Blah1]
  ,[Blah2]
)
-- in the output clause you can access the inserted/deleted pseudo tables:
ouptut inserted.[Id]
into @NewlyInsertedIDs
(
   [Id]
)
values
(
   'Blah'
  ,'Blah'
);

select
 @id = [Id]
from @NewlyInsertedIds;

Check out the OUTPUT Clause for more information.

Help with T-SQL script: Insert record, then use identity of that insert on another statement?

2 Answers2

Linked