Use the right tool to get identity values back after an insert, Sql Server

Use the right tool to get identity values back after an insert

-- First, lets create a couple of tables with identity columns:

CREATE TABLE TblIdentityDemo
(
    id int identity(1,1),
    col int NOT NULL CONSTRAINT PK_TblIdentityDemo PRIMARY KEY
);

CREATE TABLE TblTriggeredWithIdentity
(
    id int identity(-10,-10),
    col int CONSTRAINT DF_TblTriggeredWithIdentity_Col DEFAULT(0)
);
GO

-- Then, create an after insert trigger on one table, that will insert records to the other table.
CREATE TRIGGER TblIdentityDemo_Insert 
ON TblIdentityDemo 
FOR INSERT
AS   
    INSERT INTO TblTriggeredWithIdentity 
    DEFAULT VALUES
GO

-- And now - let's play: First, insert a couple of records into the first table:

INSERT INTO TblIdentityDemo (Col) 
VALUES (1), (2);

-- See what happens:

SELECT  TOP 1 
        'After a succesful insert' As 'demo',
        SCOPE_IDENTITY() As [Scope_Identity()], 
        IDENT_CURRENT('TblIdentityDemo') As [IDENT_CURRENT('TblIdentityDemo')], 
        @@IDentity As [@@IDentity],
        Id,
        Col
FROM TblIdentityDemo
ORDER BY Id DESC;

-- Then, a failed insert (Col is the primary key, so no duplicate values allowed!)

BEGIN TRY
    INSERT INTO TblIdentityDemo (Col) 
    VALUES (1), (2);
END TRY
BEGIN CATCH
  -- Do nothing
END CATCH

-- Now see what happens:

SELECT  TOP 1
        'After a failed insert' As 'demo',
        SCOPE_IDENTITY() As [Scope_Identity()], 
        IDENT_CURRENT('TblIdentityDemo') As [IDENT_CURRENT('TblIdentityDemo')], 
        @@IDentity As [@@IDentity],
        Id,
        Col
FROM TblIdentityDemo
ORDER BY Id DESC;

-- And finnaly, a taste of the output clause:

INSERT INTO TblTriggeredWithIdentity (Col) 
OUTPUT 'output' as 'demo', Inserted.id, Inserted.Col 
VALUES (1), (2), (3), (4);


run \| edit \| history \| help	0