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

Question

I was asked if you could have an insert statement in which there was an identifier 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 statement statement.

Is this possible (SQL Server 2008r2)?

Thanks.

Answer 1

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

However, you can use, for example, 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 will fire immediately after any rows are inserted and set the OtherID column to the IDENTITY column values ​​for the inserted rows. But this is strictly speaking not inside the same operator - it is right after your initial statement.

Answer 2

You can do this by specifying the 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 

Answer 3

About the cheked answer:

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

marc_s I suppose you are actually wrong. Yes, he can! )))

Solution: IDENT_CURRENT() :

 CREATE TABLE TemporaryTable( Id int PRIMARY KEY IDENTITY(1,1) NOT NULL, FkId int NOT NULL ) ALTER TABLE TemporaryTable ADD CONSTRAINT [Fk_const] FOREIGN KEY (FkId) REFERENCES [TemporaryTable] ([Id]) INSERT INTO TemporaryTable (FkId) VALUES (IDENT_CURRENT('[TemporaryTable]')) INSERT INTO TemporaryTable (FkId) VALUES (IDENT_CURRENT('[TemporaryTable]')) INSERT INTO TemporaryTable (FkId) VALUES (IDENT_CURRENT('[TemporaryTable]')) INSERT INTO TemporaryTable (FkId) VALUES (IDENT_CURRENT('[TemporaryTable]')) UPDATE TemporaryTable SET [FkId] = 3 WHERE Id = 2 SELECT * FROM TemporaryTable DROP TABLE TemporaryTable 

Moreover, you can even use IDENT_CURRENT() as DEFAULT CONSTRAINT , and it works instead of SCOPE_IDENTITY() , for example. Try the following:

 CREATE TABLE TemporaryTable( Id int PRIMARY KEY IDENTITY(1,1) NOT NULL, FkId int NOT NULL DEFAULT IDENT_CURRENT('[TemporaryTable]') ) ALTER TABLE TemporaryTable ADD CONSTRAINT [Fk_const] FOREIGN KEY (FkId) REFERENCES [TemporaryTable] ([Id]) INSERT INTO TemporaryTable (FkId) VALUES (DEFAULT) INSERT INTO TemporaryTable (FkId) VALUES (DEFAULT) INSERT INTO TemporaryTable (FkId) VALUES (DEFAULT) INSERT INTO TemporaryTable (FkId) VALUES (DEFAULT) UPDATE TemporaryTable SET [FkId] = 3 WHERE Id = 2 SELECT * FROM TemporaryTable DROP TABLE TemporaryTable 

Answer 4

You can do both.

To insert rows with a column id, you need set identity_insert off .

Please note that you still cannot duplicate values!

Here you can see the team here . Note the set identity_insert on after.

To create a table with the same record, you just need to:

• create a new column;
• insert it with a zero value or another thing;
• refresh this column after inserts with the value of the identity column.

If you need to insert a value at the same time, you can use the global variable @@identity . He will give you the last one inserted. So I think you need to do @@identity + 1 . In this case, it may give incorrect values, because @@identity intended for all tables. Thus, this will be considered if the insert appears in another table with an identifier.

Another solution is to get the maximum id and add one :) and you will get the desired value!

Answer 5

use this simple code `SCOPE_IDENTITY () + 1