SQL 中主標識列IDENTITY使用技巧

本文通過一個操作實例來說明SQL中主標識列IDENTITY的使用技巧。

要求：在 SQL Server 2005中，建立數據表book，在表book中設有標識列，標識種子為1000，現要從另一個相同結構的表book1中導入數據，兩表結構定義語句如下：

create table book
(書號 int identity(1000,1) not for identity primary key clustered,
書名 char(20) not null,
出版社 char(20) check (出版社 in ('高教','浙大','電子','中央')),
出版日期 datetime default (getdate()),
單價 numeric(7,2) check(單價>0),
數量 smallint check(數量>=0),
總價 AS 單價*數量,
EMAIL varchar(20) check(EMAIL LIKE '%@%') create table book1
(書號 int primary key clustered,
書名 char(20) not null,
出版社 char(20) check (出版社 in ('高教','浙大','電子','中央')),
出版日期 datetime default (getdate()),
單價 numeric(7,2) check(單價>0),
數量 smallint check(數量>=0),
總價 AS 單價*數量,
EMAIL varchar(20) check(EMAIL LIKE '%@%')

用如下語句來導入數據：

insert into book select * from book1

結果，系統報錯為：

當使用了列列表並且IDENTITY_INSERT 為ON 時，才能為表‘book’中的標識列指定顯式值。

問題1：SQL Server 2005中如何設置IDENTITY_INSERT的值為ON？

格式為：SET IDENTITY_INSERT TABLE_NAME ON/OFF

於是用下列語句

SET IDENTITY_INSERT book ON

Insert into book select * from book1

SET IDENTITY_INSERT book OFF

結果出錯提示與上面相同。為什麼已經設置了表book的IDENTITY_INSERT為ON還出錯呢？

重新輸入指定列名的的語句：

insert into book(書號,書名) select 書號,書名 from book2

結果：系統提示成功運行。Book2中的記錄按記錄號的順序插入到表book中。

結論：

這說明在主表定義標識列且加選項not for replication時，表示當通過復制向表中插入數據時，不需要遵循IDENTITY屬性要求。 在復制數據時，要保證主表的IDENTITY_INSERT的狀態為ON，且復制數據時必須顯示的表明字段名。