T-SQL script help: Insert a record and then use the identity of this insert in another expression?

Question

As a preface, I am not very good at T-SQL syntax.

I would like to create a simple SQL script that will do 3 insert statements.

Insert A
Insert B
Insert C

Paste The application identifier or "ID" is required in the Paste instruction. And both Insert C and B identifiers are required in Insert C.

The pseudocode will look something like this:

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 can I write this script?

Answer 1

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

, :

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() int (+1 btw). , , , - :

declare 
 @ID uniqueidentifier
,@NewlyInsertedIds table ([ID] uniqueidentifier) -- a table variable for storing your newly inserted IDs

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

select @ID = [ID] from NewlyInsertedIds