Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Inside a TSQL transaction I have an operation of inserting a record into a MyTable1 and an operation of updating a MyTable2 with a value of Identity column of the MyTable1 record, which is only to be inserted after the transaction is commited.
So, how to get this autogenerated value before it is actually inserted into the table?

Code just 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 
like image 243
rem Avatar asked Dec 03 '12 12:12

rem


People also ask

How do I find the identity column value?

Retrieving IDENTITY column values with @@identityUse the @@identity global variable to retrieve the last value inserted into an IDENTITY column. The value of @@identity changes each time an insert or select into attempts to insert a row into a table.

How do you check if a transaction is committed or not?

XACT_STATE() reports the transaction state of a session, indicating whether or not the session has an active transaction, and whether or not the transaction is capable of being committed.

How do you identify that a column is an identity column in SQL?

If a column is an identity column then sys. columns. is_identity is 1 else it is 0. This is how we can list all identity columns for all tables in a database.


1 Answers

Use SCOPE_IDENTITY() just after the INSERT

--
DECLARE @newId INT 
INSERT INTO MyTable1(
   Field1
)
SELECT @MyVal1
--End of Insert here
SELECT @newId = SCOPE_IDENTITY()
--Rest of the procedure
like image 149
Kaf Avatar answered Sep 18 '22 10:09

Kaf