Home > Functions and DMVs, SQL Queries, SQL Server, Stored Procedures, T-SQL > Insert values into IDENTITY column of SQL server table / Disable IDENTITY Column in SQL server 2005

Insert values into IDENTITY column of SQL server table / Disable IDENTITY Column in SQL server 2005

September 15, 2009 Leave a comment Go to comments

Identity columns automatically inserts value for each record inserted into the table . These columns are commonly used as primary keys.  Usually developers face problems while inserting records into these columns, It’s actually very easy to do.

Let me show this to you, with an example. Create a table using the query

CREATE TABLE TestTable (
	TestID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
	TestValue NVARCHAR(50) NOT NULL )

now try inserting records into this table using

INSERT TestTable(TestId, TestValue)
VALUES (1, 'record 1')

you will get an error stating

Cannot insert explicit value for identity column in table 'TestTable' 
when IDENTITY_INSERT is set to OFF.

The right way of doing inserting records , is by enabling the property "IDENTITY_INSERT" to ON.

SET IDENTITY_INSERT TestTable ON
INSERT TestTable(TestId, TestValue)
VALUES (1, 'record 1')

SET IDENTITY_INSERT TestTable OFF

By enabling the property, the record can be inserted into the table. There are some key points  that you need to remember, when it comes to identity inserts

  • You must specify a value for the identity column, after enabling it on a table.
  • It can only be enabled on one table at a time. If you try to do it for more than one table , you will receive an error message.
  • The table should be owned by the user executing the query.
  • you need to mention the fields in the insert statement. It might throw an error otherwise. that is, the highlighted part in the below query is mandatory for insert.
INSERT TestTable(TestId, TestValue)
VALUES (1, 'record 1')
About these ads
  1. Niral
    March 10, 2010 at 9:44 am

    its good article. we get to know about identity column.Thanks a lot

  2. June 26, 2013 at 9:38 am

    Can you help me code SQL , I have 2 table with data the seem, but when I copy data from FolioTransaction to BKFolioTransaction the system error:

    Msg 8101, Level 16, State 1, Line 1
    An explicit value for the identity column in table ‘SMILE_FO.dbo.BKFolioTransaction’ can only be specified when a column list is used and IDENTITY_INSERT is ON.

    I know 2 table have [TransactionID] [numeric](18, 0) IDENTITY(1,1) NOT NULL, I can’t not copy :(

    Can you help me???

    Your code:

    SET IDENTITY_INSERT dbo.BKFolioTransaction ON;

    INSERT INTO SMILE_FO.dbo.BKFolioTransaction
    select * from SMILE_FO.dbo.FolioTransaction — where RefNumber =@Checkno

    SET IDENTITY_INSERT dbo.BKFolioTransaction off;

    Thank alot

  3. June 26, 2013 at 9:40 am

    Can you help me code SQL , I have 2 table with data the seem, but when I copy data from FolioTransaction to BKFolioTransaction the system error:

    Msg 8101, Level 16, State 1, Line 1
    An explicit value for the identity column in table ‘SMILE_FO.dbo.BKFolioTransaction’ can only be specified when a column list is used and IDENTITY_INSERT is ON.

    I know 2 table have [TransactionID] [numeric](18, 0) IDENTITY(1,1) NOT NULL, I can’t not copy :(

    Can you help me???

    Your code:

    SET IDENTITY_INSERT dbo.BKFolioTransaction ON;

    INSERT INTO SMILE_FO.dbo.BKFolioTransaction
    select * from SMILE_FO.dbo.FolioTransaction — where RefNumber =@Checkno

    SET IDENTITY_INSERT dbo.BKFolioTransaction off;

  1. September 18, 2009 at 8:02 am

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

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: