TSQL: Identity

SET IDENTITY_INSERT
Allows explicit values to be inserted into the identity column of a table.

Syntax:
SET IDENTITY_INSERT <tablename> ON

Sample:
SET IDENTITY_INSERT temp2 ON

Notes:

• Only one table in a session can have the IDENTITY_INSERT property set to ON.

How to get the newly inserted identity value:

I have a sample table called temp:

create table temp
(tempid int identity(1,1),
datemodified date)


To get the identity value on the newly inserted row:

declare @TableVar table(tempid int);

insert into temp output inserted.tempid into @TableVar
values(GETDATE());

select tempid from @TableVar;


DBCC CHECKIDENT

• Resets the current identity value if needed. 

          DBCC CHECKIDENT ('temp');

          Result:
          Checking identity information: current identity value '3', current column value '3'.

• Reports the current identity value, and does not correct the identity value if it is needed

            DBCC CHECKIDENT ('temp', NORESEED);

• Force the current identity value to a new value:

           DBCC CHECKIDENT ('temp', RESEED, 10)

          Result:
          Checking identity information: current identity value '4', current column value '10'.