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
