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
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.
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.
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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With