SQL : Accessing identity column value for current insertion

Question

TABLE -> Example (A,B,C,D)

A is an identity column. How can I do:

INSERT INTO Example(B,C,D) VALUES (b,c, 'valueOF(A) sometext');

I.e. I want to access the value of identity column 'A' in column D during insertion. Is it possible?

Answer 1

Yes with output keyword:

DECLARE @Result TABLE(A BIGINT)

INSERT INTO Example(B, C, D) 
OUTPUT INSERTED.A INTO @Result 
VALUES (b, c, '');

Now you have in @Result table inserted value of column A.

UPDATE e SET d = CAST(r.A as varchar(10)) + 'some text'
FROM Example e
JOIN @Result r on e.A = r.A

If you have Sql Server 2012+ then you can use sequential object. Remove identity from column A. Then create a sequence:

CREATE SEQUENCE dbo.Sequencename AS INT
MINVALUE 1
CYCLE;

Then use it like:

DECLARE @a INT    
SELECT @a = NEXT VALUE FOR dbo.Sequencename

INSERT INTO Example(A, B, C, D) 
VALUES (@a, b, c, CAST(@a as varchar(10)) + 'some text');