SQL Server/T-SQL Tutorial/Sequence Indentity/IDENTITY INSERT

SET IDENTITY_INSERT Employee ON

   <source lang="sql">

INSERT Employee (DepartmentID, Name, GroupName) VALUES (17, "Database Services", "Information Technology") SET IDENTITY_INSERT Employee OFF</source>

Using the IDENTITY_INSERT Session Option

   <source lang="sql">

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></source>