您現在的位置: 365建站網 > 365學習 > set IDENTITY_INSERT的用法和設置為OFF時異常的解決方法

set IDENTITY_INSERT的用法和設置為OFF時異常的解決方法

文章來源:365jz.com     點擊數:771    更新時間:2017-12-10 09:19   參與評論

想要將值插入到自動編號(或者說是標識列,IDENTITY)中去,需要設定 SET IDENTITY_INSERT

示例:

1.首先建立一個有標識列的表:
CREATE TABLE products (id int IDENTITY PRIMARY KEY, product varchar(40))

2.嘗試在表中做以下操作:
INSERT INTO products (id, product) VALUES(3, 'garden shovel')

結果會導致錯誤:“當 IDENTITY_INSERT 設置為 OFF 時,不能向表 'products' 中的標識列插入顯式值。”

3.改用:
SET IDENTITY_INSERT products ON
INSERT INTO products (id, product) VALUES(1, 'garden shovel')

返回正確。

4.建立另外一個表products2,嘗試相同插入操作:
CREATE TABLE products2 (id int IDENTITY PRIMARY KEY, product varchar(40))

然后執行:
SET IDENTITY_INSERT products2 ON
INSERT INTO products2 (id, product) VALUES(1, 'garden shovel')

導致錯誤:“表 'material.dbo.products' 的 IDENTITY_INSERT 已經為 ON。無法對表 'products2' 執行 SET 操作。”

改為執行:
SET IDENTITY_INSERT products OFF
SET IDENTITY_INSERT products2 ON
INSERT INTO products2 (id, product) VALUES(2, 'garden shovel')

執行通過。

5.嘗試以下操作:
SET IDENTITY_INSERT products2 ON
INSERT INTO products2     SELECT * FROM products

導致錯誤:“僅當使用了列的列表,并且 IDENTITY_INSERT 為 ON 時,才能在表 'products2' 中為標識列指定顯式值。”

6.改為:
SET IDENTITY_INSERT products2 ON
INSERT INTO products2     (id, product)     SELECT * FROM products

執行通過。

總結:

1.每一次連接會話中的任一時刻,只能對一個表設置IDENTITY_INSERT ON,且設置只對當前會話有效;
2.在對標識列執行插入操作進,一定要列出此標識列(當然,同時也就需要列出相關的其他列了)。

附:
SQL Server幫助文檔相關內容

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 的設置是在執行或運行時設置,而不是在分析時設置。

權限
執行權限默認授予 sysadmin 固定服務器角色和 db_owner 及 db_ddladmin 固定數據庫角色以及對象所有者。

示例
下例創建一個含有標識列的表,并顯示如何使用 SET IDENTITY_INSERT 設置填充由 DELETE 語句導致的標識值中的空隙。

-- Create products table.
CREATE TABLE products (id int IDENTITY PRIMARY KEY, product varchar(40))
GO
-- Inserting values into products table.
INSERT INTO products (product) VALUES ('screwdriver')
INSERT INTO products (product) VALUES ('hammer')
INSERT INTO products (product) VALUES ('saw')
INSERT INTO products (product) VALUES ('shovel')
GO

-- Create a gap in the identity values.
DELETE products 
WHERE product = 'saw'
GO

SELECT * 
FROM products
GO

-- Attempt to insert an explicit ID value of 3;
-- should return a warning.
INSERT INTO products (id, product) VALUES(3, 'garden shovel')
GO
-- SET IDENTITY_INSERT to ON.
SET IDENTITY_INSERT products ON
GO

-- Attempt to insert an explicit ID value of 3
INSERT INTO products (id, product) VALUES(3, 'garden shovel').
GO

SELECT * 
FROM products
GO
-- Drop products table.
DROP TABLE products
GO

qlserver 批量插入記錄時,對有標識列的字段要設置 set IDENTITY_INSERT 表名 on,然后再執行插入記錄操作;插入完畢后恢復為 off 設置

格式:
  set IDENTITY_INSERT 表名 on
  set IDENTITY_INSERT 表名 off


舉例:

set IDENTITY_INSERT peoplePworkpositiontype  on
insert peoplePworkpositiontype(id,workpositiontype,workpositiontypeid) values(1 , '平臺' , 1 ) 
insert peoplePworkpositiontype(id,workpositiontype,workpositiontypeid) values(2 , '陸地' , 2 ) 
insert peoplePworkpositiontype(id,workpositiontype,workpositiontypeid) values(3 , '海上' , 3 )

go
set IDENTITY_INSERT peoplePworkpositiontype  off


set IDENTITY_INSERT peoplePstatetype  on
insert peoplePstatetype(id,nowstatetype,nowstatetypeid) values(1 , '出海' , 1 ) 
insert peoplePstatetype(id,nowstatetype,nowstatetypeid) values(2 , '出差' , 2 ) 
insert peoplePstatetype(id,nowstatetype,nowstatetypeid) values(3 , '公司' , 3 ) 
insert peoplePstatetype(id,nowstatetype,nowstatetypeid) values(4 , '會議' , 4 ) 
go
set IDENTITY_INSERT peoplePstatetype  off
go
 

當 IDENTITY_INSERT 設置為 OFF 時,不能向表"A" 中的標識列插入顯示值。

解決方法:

1.有一項數據(message_id)如是自動添寫的。把那列刪掉就可以寫進去了。 具體為:

原始:"insert into user_message(message_id,user_id,message_content) Values(11,333,'hahaha')";  //報錯

改成:"insert into user_message(user_id,message_content) Values(333,'hahaha')";

 2.先運行“SET IDENTITY_INSERT 表名 ON”,再運行你的插入語句,再執行“SET IDENTITY_INSERT STREAM OFF”就可以了。


如對本文有疑問,請提交到交流論壇,廣大熱心網友會為你解答??! 點擊進入論壇


發表評論 (771人查看,0條評論)
請自覺遵守互聯網相關的政策法規,嚴禁發布色情、暴力、反動的言論。
用戶名: 驗證碼: 點擊我更換圖片
最新評論
------分隔線----------------------------
自拍偷拍福力视频,偷拍国际精品,麻豆一区福利电影,国产网红视频午夜福利,se视频大全,久久国产AV影院 国产三级级在线电影| 又黄又粗暴的gif动态图| 亚洲高清有码中文字| 人禽交vide欧美| 久久青草国产免费频观| 日本猛片在线观看| 97热久久免费频精品99| 学生xxxxx性| 青青青视频香蕉在线观看视频| 国产成人av在线影院| 坡多野结衣的在线播放| 欧美日韩在线无码一区二区| 偷拍农村老熟女69式视频| 老师你下面太紧进不去| 欧美av毛片基地视频在线观看| 午夜男女生活片牲交| 五月丁香啪啪激情综合色九色| 直接观看黄网站免费| 国产六月婷婷爱在线观看| 天天夜日日在线观看| 欧美视频在线电影| 中文无遮挡h肉视频在线观看| 人妻在厨房被侮辱电影| 国产v亚洲v欧美v专区| 欧美老熟妇乱xxxxx| 午夜男女真人做爽爽爽视频| 四虎永久在线精品免费观看| 邻居三个老汉一起弄我| 亚洲最大无码av网站观看| 日本无码不卡的岛国片| 女人自熨全过程(有声)| http://www.dxcmjs.com