Set IDENTITY_INSERT的用法
2024-08-31 01:02:30
供稿：網友
今天想往數據庫中插入一些數據，包括標識列，可老是插入失敗，提示IDENTITY_INSERT設置為off,不能插入顯示數據，后來試了幾種方法，老是失敗，如:
select * from goods
sp_help goods
exec sp_dboption lei
--有錯exec sp_dboption 'IDENTITY_INSERT' true
--有錯alter database lei set identity_insert on
--有錯exec(set identity_insert '+@貨號+' on )
后來突然間腦門一亮，哎，不就是這句話么，set identity_insert <表名> on
在此也給大家推薦一下Set IDENTITY_INSERT的用法
1.首先看一看微軟幫助上的說法
SET IDENTITY_INSERT
允許將顯式值插入表的標識列中。
語法
SET IDENTITY_INSERT [ database.[ owner.] ] { table } { ON | OFF }
參數
database
是指定的表所駐留的數據庫名稱。
owner
是表所有者的名稱。
table
是含有標識列的表名。
注釋
任何時候，會話中只有一個表的 IDENTITY_INSERT 屬性可以設置為 ON。如果某個表已將此屬性設置為 ON，并且為另一個表發出了 SET IDENTITY_INSERT ON 語句，則 Microsoft® SQL Server? 返回一個錯誤信息，指出 SET IDENTITY_INSERT 已設置為 ON 并報告此屬性已設置為 ON 的表。
如果插入值大于表的當前標識值，則 SQL Server 自動將新插入值作為當前標識值使用。
SET IDENTITY_INSERT 的設置是在執行或運行時設置，而不是在分析時設置。
2.我對SET IDENTITY_INSERT用法及理解
2.1這個是干什么用的？
SET IDENTITY_INSERT [TABLENAME] {ON|OFF}從字面的意思看是用來在插入主鍵時設置主鍵狀態的。這個主鍵指的是自增長的主鍵。
2.2使用方法
2.2.1首先，這條語句只對自增長的主鍵生效，經過在查詢分析器上的試驗，如果主鍵不為自增長類型會報如下錯誤：
服務器: 消息 8106，級別 16，狀態 1，行 1
表 'test1' 沒有標識屬性。無法執行 SET 操作。
也是說他和自增長這個標識有關系。
2.2.2其次，這條語句是用在“執行時或運行時，而不是分析時”。根據我的理解，我認為意思是，當存儲過程或是sql語句執行時執行，而不是在建立表的分析時執行。他的執行可以永久的改變狀態，但是查看主鍵的狀態時卻不發生變化。雖然展示找不到他把這個狀態存儲在哪里，但我想，應該存儲在系統表中。
2.2.3這是我在查詢分析器上做了試驗的幾條語句：
2.2.3-1表test1，有非自增長字段id（int），v(char)，執行insert into wangwh.dbo.test1 (id,v)values(1,'test')，結果為：
服務器: 消息 8106，級別 16，狀態 1，行 1
表 'test1' 沒有標識屬性。無法執行 SET 操作。
2.2.3-2表test，有自增長字段id（int），v（char）,將下邊的語句分開執行。
//直接插入一條數據
insert into wangwh.dbo.test (id,v)values(1,'test')將提示
服務器: 消息 544，級別 16，狀態 1，行 1
當 IDENTITY_INSERT 設置為 OFF 時，不能向表 'test1' 中的標識列插入顯式值。
//進行SET IDENTITY_INSERT 設置后，插入1條數據
set identity_insert wangwh.dbo.test1 on
insert into wangwh.dbo.test1 (id,v)values(1,'test')將提示
set identity_insert wangwh.dbo.test1 off
（所影響的行數為 1 行）
2.2.3-3如果將狀態設置on的話將可以一直插入如果不希望一直能插入的話，還需要進行off的設置。因為一旦狀態設置為on那么下次插入時就必須連同ID一起插入，否則就會報出如下錯誤：
服務器: 消息 545，級別 16，狀態 1，行 1
當 IDENTITY_INSERT 設置為 ON 時，必須指定表 'test1' 中標識列的顯式值。
