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?

Answer 1

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)

Answer 2

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.