Просмотр исходного текста страницы SQL Server/T-SQL Tutorial/Sequence Indentity/IDENTITY INSERT

== SET IDENTITY_INSERT Employee ON==



   
  <!-- start source code -->
   
    <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==



   
  <!-- start source code -->
   
    <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>