Mega Code Archive
Using the IDENTITY_INSERT Session Option
<![CDATA[
If you want to supply your own explicit values for the IDENTITY column in an INSERT statement, you have to turn the session option IDENTITY_INSERT to ON for your table.
You can't update an IDENTITY column.
8>
9> CREATE TABLE MyTable (
10> key_col int NOT NULL IDENTITY (1,1),
11> abc char(1) NOT NULL
12> )
13> INSERT INTO MyTable VALUES ('a')
14> INSERT INTO MyTable VALUES ('b')
15> INSERT INTO MyTable VALUES ('c')
16> SELECT * FROM MyTable ORDER BY key_col
17>
18>
19> SET IDENTITY_INSERT MyTable ON
20>
21> INSERT INTO MyTable (key_col, abc) VALUES(2, 'g')
22> SELECT
23> *
24> FROM
25> MyTable
26> ORDER BY
27> key_col
28> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
key_col abc
----------- ---
1 a
2 b
3 c
(1 rows affected)
key_col abc
----------- ---
1 a
2 b
2 g
3 c
(4 rows affected)
1>
2> SET IDENTITY_INSERT MyTable OFF
3>
4> drop table MyTable
5> GO
1>
]]>