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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s