CODEWITHSUNDEEP
sql-servertransactions
1950
1950

Reputation: 191

Get Id from first table in transaction

I got this two tables for example image

and I need to made transaction that input data in this tables. How can I get Id that is input in firstTable, and set it as Foreign key FirstTableId in second table?

BEGIN TRANSACTION
INSERT INTO FirsTable (Name) VALUES ('example1')

INSERT INTO SecondTable (Name, FirstTableId) VALUES ('example2', ?)

COMMIT TRANSACTION

Upvotes: 1

Views: 574

Answers (1)

ahmed abdelqader
ahmed abdelqader

Reputation: 3568

Considering id is an identity, Use @@IDENTITY as next:-

BEGIN TRANSACTION
INSERT INTO FirsTable (Name) VALUES ('example1')

INSERT INTO SecondTable (Name, FirstTableId) VALUES ('example2', @@IDENTITY)

COMMIT TRANSACTION

For More details.

UPDATE:-

For Non identity columns, Use table variable to get id via using Output, as next:-

BEGIN TRANSACTION
DECLARE @id int
DECLARE @table table (id int)

INSERT INTO FirsTable (Name) 
OUTPUT inserted.id into @table
VALUES ('example1')
SELECT @id = id from @table

INSERT INTO SecondTable (Name, FirstTableId) VALUES ('example2',  @id)

COMMIT TRANSACTION

For More details

Upvotes: 2

Related Questions