Capture IDENTITY column value during insert and use as value for another column in same transaction

Question

While performing an insert to a table which has an IDENTITY column, is it possible to use the IDENTITY value as the value for another column, in the same transaction?

For example:

DECLARE @TestTable TABLE 
                   (
                        PrimaryId INT NOT NULL IDENTITY(1, 1), 
                        SecondaryId INT NOT NULL
                   );

INSERT INTO @TestTable (SecondaryId)
    SELECT 
        SCOPE_IDENTITY() + 1; -- set SecondaryId = PrimaryId + 1

SELECT * FROM @TestTable;

Expected:

| PrimaryId | SecondaryId |
+-----------+-------------+
|     1     |      2      |

I thought I might be able to achieve this with the SCOPE_IDENTITYor @@IDENTITY system functions, but unfortunately this does not work, as it is NULL at the time the transaction is executed.

Cannot insert the value NULL into column 'SecondaryId', table '@TestTable'; column does not allow nulls. INSERT fails.

I know I could use a computed column for this example, but I'm curious if what I'm trying to do is even possible.

Answer 1

Could you change your approach and use a SEQUENCE instead of an IDENTITY column?

CREATE SEQUENCE TestSequence
    START WITH 1  
    INCREMENT BY 1 ;  
GO
CREATE TABLE TestTable (PrimaryId INT NOT NULL DEFAULT NEXT VALUE FOR TestSequence, SecondaryId INT NOT NULL);

GO

INSERT INTO TestTable (
    SecondaryId
)
SELECT  NEXT VALUE FOR TestSequence + 1; -- set SecondaryId = PrimaryId + 1;

GO 3

SELECT * FROM TestTable;

GO
DROP TABLE TestTable;
DROP SEQUENCE TestSequence;

Answer 2

I would go with a trigger, this should also work for multi row inserts, You will need to remove the not null for SecondaryID, not sure if that's acceptable.

create trigger trg_TestTable
   on    dbo.TestTable
   after insert 
AS 
BEGIN
    update TestTable
    set    SecondaryId = i.PrimaryId
    from   inserted  i
    join   TestTable a
      on   i.PrimaryId = a.PrimaryId;
END
GO

Answer 3

One thing you could do is use the OUTPUT INSERTED option of the INSERT COMMAND to capture the IDENTITY. In this example the IDENTITY field is ScheduleID.

CREATE PROCEDURE dbo.spScheduleInsert
            (   @CustomerID int,
                @ItemID int,
                @StartDate Date,
                @TimeIn DateTime,
                @TimeOut DateTime,
                @ReturnIdentityValue int OUTPUT )
AS
BEGIN
            DECLARE @TempScheduleIdentity table ( TempScheduleID int )

            INSERT INTO Schedule ( CustomerID,ItemID,StartDate,TimeIn,TimeOut )
            OUTPUT INSERTED.ScheduleID into @TempScheduleIdentity
            VALUES  (@CustomerID,@ItemID,@StartDate,@TimeIn,@TimeOut)

            SELECT @ReturnIdentityValue = (SELECT TempScheduleID FROM @TempScheduleIdentity)

END

Once you have the @ReturnIdentityValue...you could then update the records other field with the value.