Get IDENTITY value in the same T-SQL statement it is created in?

Question

I was asked if you could have an insert statement, which had an ID field that was an "identity" column, and if the value that was assigned could also be inserted into another field in the same record, in the same insert statement.

Is this possible (SQL Server 2008r2)?

Thanks.

Answer 1

You cannot really do this - because the actual value that will be used for the IDENTITY column really only is fixed and set when the INSERT has completed.

You could however use e.g. a trigger

CREATE TRIGGER trg_YourTableInsertID ON dbo.YourTable 
AFTER INSERT
AS 
UPDATE dbo.YourTable
SET dbo.YourTable.OtherID = i.ID
FROM dbo.YourTable t2
INNER JOIN INSERTED i ON i.ID = t2.ID

This would fire right after any rows have been inserted, and would set the OtherID column to the values of the IDENTITY columns for the inserted rows. But it's strictly speaking not within the same statement - it's just after your original statement.

Answer 2

You can do this by having a computed column in your table:

 DECLARE    @QQ TABLE (ID INT IDENTITY(1,1), Computed AS ID PERSISTED, Letter VARCHAR (1))

INSERT INTO @QQ (Letter)
VALUES ('h'),
('e'),
('l'),
('l'),
('o')

SELECT  *
FROM    @QQ

1   1   h

2   2   e

3   3   l

4   4   l

5   5   o