How to insert explicit values into the identity columns in SQL server


SET IDENTITY_INSERT [ database. [ owner. ] ] { table } { ON | OFF }




Msg 544, Level 16, State 1, Line 1
Cannot insert explicit value for identity column in table ‘CHECKIDENT’ when IDENTITY_INSERT is set to OFF.

USE [SQLJUNKIESHARE]
GO

CREATE TABLE [dbo].[CHECKIDENT](
[ID] [int] IDENTITY(1,1) NOT NULL,
[date] [date] NULL,
[descp] [varchar](10) NULL
) ON [PRIMARY]

GO

-- How to insert explicit values in identity columns with out altering table

INSERT INTO DBO.CHECKIDENT(ID,descp)

VALUES(1,'CHECK')

-- WHEN SET TO ON WE CAN INSERT EXPLICIT VALUES IN TO THE TABLE

SET IDENTITY_INSERT DBO.CHECKIDENT ON

INSERT INTO DBO.CHECKIDENT(ID,descp)

VALUES(1,'CHECK')

INSERT INTO DBO.CHECKIDENT(ID,descp)

VALUES(1,'CHECK')

SELECT * FROM DBO.CHECKIDENT

SET IDENTITY_INSERT DBO.CHECKIDENT OFF