IDENTITY_INSERT

If you have a Dimension table for example with Identity column. You want to insert a default record with the Surrogate Key of -1 with description "Unknown".

Table with the following structure:
CREATE TABLE Dim_Manager
(
ManagerId INT IDENTITY (1,1) NOT NULL,
Name VARCHAR(50) NOT NULL
)

If you create the table with the above statement and would like to insert default dimension record. You can do that by doing the following:

SET IDENTITY_INSERT dbo.Dim_Manager ON
INSERT INTO Dim_Manager (ManagerId, Name)
VALUES('-1','Unknown')
SET IDENTITY_INSERT dbo.Dim_Manager OFF

You need to set the IDENTITY_INSERT to ON before inserting into Identity colum. Once you have inserted the record you need to set IDENTITY_INSERT to OFF as shown above.

Thanks
Sutha