摘要:set identity_insert on
若要 insert 值到identity 欄位時，需先 set identity_insert on (僅對當下session有作用)
USE [tempdb]
GO
CREATE TABLE [TestTable1](
[C1] [int] IDENTITY(1,1) NOT NULL,
[C2] [char](10) NULL
)
GO
insert into TestTable1 values(1,'test')
-- insert 值到identity欄位，會引發以下error
/*
Msg 8101, Level 16, State 1, Line 1
An explicit value for the identity column in table 'TestTable1' can only be specified when
a column list is used and IDENTITY_INSERT is ON.
*/
set identity_insert TestTable1 on
insert into TestTable1 (C1,C2) values(1,'test') -- 需寫出欄位名稱
CREATE TABLE [TestTable2](
[C1] [int] IDENTITY(1,1) NOT NULL,
[C2] [char](10) NULL
)
GO
- -同一時間僅能有一table 的identity_insert為on
set identity_insert TestTable2 on
/*
Msg 8107, Level 16, State 1, Line 2
IDENTITY_INSERT is already ON for table 'tempdb.dbo.TestTable1'. Cannot perform SET operation for table 'TestTable2'.
*/
set identity_insert TestTable1 off
set identity_insert TestTable2 on