Geek Answers Handbook

How to get the value of an Identity column (not yet committed) inside a transaction

Inside the TSQL transaction, I have an insert insert operation in MyTable1 and an update operation MyTable2 with the value of the Identity column of the record MyTable1 , which should be inserted after the transaction.
So, how to get this auto-generated value before it is actually inserted into the table?

Code to illustrate the question:

 CREATE TABLE MyTable1( MyTable1Id int identity(1,1) primary key, Field1 varchar(50) ) CREATE TABLE MyTable2( MyTable2Id int identity(1,1) primary key, MyTable1Id int ) CREATE PROCEDURE MyProc( @MyVal1 [varchar](50) ) AS BEGIN TRAN -- try to insert INSERT INTO MyTable1( Field1 ) SELECT @MyVal1 IF @@ERROR <> 0 BEGIN ROLLBACK TRAN END -- update MyTable2 DECLARE @TheUnknownIdValue int UPDATE MyTable2 SET MyTable1Id = @TheUnknownIdValue -- how to get the value needed here? WHERE ... IF @@ERROR <> 0 BEGIN ROLLBACK TRAN END COMMIT TRAN
+4
source share
4 answers

Use SCOPE_IDENTITY() immediately after INSERT

 -- DECLARE @newId INT INSERT INTO MyTable1( Field1 ) SELECT @MyVal1 --End of Insert here SELECT @newId = SCOPE_IDENTITY() --Rest of the procedure
+5
source

Get the last generated value. The most common way is to use the built-in function SCOPE_IDENTITY () .

In addition, you also have the function @@ IDENTITY and IDENT_CURRENT ('TableName') .

 Select SCOPE_IDENTITY() Select @@IDENTITY Select IDENT_CURRENT('tblPerson')

SCOPE_IDENTITY () - Returns the last identity value created in the same session and in the same scope.

@@ IDENTITY - Returns the last identity value created in the same session and in any scope.

IDENT_CURRENT ('TableName') - returns the last identifier value created for a specific table in any session and in any area.

Example:

SCOPE_IDENTITY () returns the last identity value created in the same session ( Connection ) and in the same scope ( in the same Stored procedure, function, trigger ).

Say I have 2 tables tblPerson1 and tblPerson2, and I have a trigger on table tblPerson1 that inserts an entry into table tblPerson2. Now, when you insert an entry into the tblPerson1 table, SCOPE_IDENTITY () returns the idetentity value that is created in the tblPerson1 table

Where, like @@ IDENTITY, returns the value generated in the tblPerson2 table. Thus, @@ IDENTITY returns the last identity value created in the same session, without any consideration of the scope.

IDENT_CURRENT ('tblPerson') returns the last authentication value created for a particular table, in any session and in any area.

+3
source

If you insert one record at a time, you can use the scope_identity () function:

 set @TheUnknownIdValue = scope_identity()

do it right after pasting into MyTable1.

In cases where you want to insert multiple rows, the best way is to use the OUTPUT clause to get all the new identifiers in the worksheet.

+2
source

in SQL Server uses SCOPE_IDENTITY (), which gives the last generated authentication value

 UPDATE MyTable2 SET MyTable1Id = SCOPE_IDENTITY() -- how to get the value needed here WHERE ...

Should you probably stop all this if the first insertion failed? Look at Try / Catch on the SQL server, you will find some good templates there.

+1
source

More articles:


All Articles