How can I insert identity manually?

Question

CREATE TABLE masterTbl (
id INT IDENTITY(1,1) CONSTRAINT pk_id PRIMARY KEY,
name VARCHAR(100))

INSERT INTO masterTbl VALUES ('ABC', 'XYZ','PQR')

Now

Select * FROM masterTbl

Result:

id  | name
---------------------
1 |  ABC
2 |  XYZ
3 |  PQR

DELETE FROM masterTbl WHERE id=1

Now

Select * FROM masterTbl

Result:

id |  name
---------------------
2 |  XYZ
3 |  PQR

Now I want to run:

INSERT INTO masterTbl (id, name) VALUES (1, 'MNO')

How is this possible?

Related (or duplicate) : http://stackoverflow.com/questions/751522/how-to-change-identity-column-values-programmatically — KV Prajapati, Sep 24 '12 at 10:54

score 68 · Accepted Answer · answered Sep 24 '12 at 10:54

68

In it's simplest form, you need to temporarily allow the insertion of identity values

SET IDENTITY_INSERT masterTbl ON
INSERT INTO masterTbl (id, name) VALUES (1, 'MNO')
SET IDENTITY_INSERT masterTbl OFF

answered Sep 24 '12 at 10:54

freefaller

Setting identiy on or off, SQL spews the the same ambiguous error: "An explicit value for the identity column in table 'X' can only be specified when a column list is used and IDENTITY_INSERT is ON" – alejandrob Aug 06 '20 at 15:02
@alejandrob - I would suggest that with 60 upvotes (and I'm assuming you're my first downvote on this answer) that my example is not the problem... check you've actually listed the correct column(s) – freefaller Aug 06 '20 at 15:20
1

What happens to the identity sequence after doing this? Is it broken or does SQL server handle this behind the scenes so you don't have to reseed the sequence? – Morgeth888 Oct 13 '22 at 13:38
@Morgeth888 - good question. If the value being inserted is **above** the current identity then the identity will increased to that new value. However, if the value being inserted is **below or equal** the current identity then the identity will remain as is – freefaller Oct 13 '22 at 15:38

score 12 · Answer 2 · answered Sep 24 '12 at 10:54

Yes you can set identity fields manually executing

SET IDENTITY_INSERT masterTbl ON

then insert your data

INSERT INTO masterTbl (id, name) VALUES (1, 'MNO') 
......

and remember to call

SET IDENTITY_INSERT masterTbl OFF

to reenable the correct functionality

score 5 · Answer 3 · edited Aug 22 '19 at 13:07

5

SET IDENTITY_INSERT masterTbl ON

edited Aug 22 '19 at 13:07

Treycos

answered Sep 24 '12 at 10:53

STO

score 0 · Answer 4 · answered Aug 14 '20 at 14:00

It is possible performing the following steps:

I. Enable identity insert, which will DISABLE SQL Server from automatically inserting values in the table's identity column:

SET IDENTITY_INSERT ON

II. Perform your "manual" insert operation, SPECIFYING all the affected column names:

INSERT INTO masterTbl (id, name)
VALUES (1, 'ABC',
        2, 'XYZ',
        4, 'PQR')

III. When you're done, reenable the auto identity value insertion by disabling the manual insert feature of earlier:

SET IDENTITY_INSERT OFF

IV. You're done!

4 Answers4