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?

Answer 1

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 

Answer 2

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