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' GOSELECT * 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'). GOSELECT * FROM products GO -- Drop products table. DROP TABLE products GO
set identity_insert A on insert A(ID,Name) select * from ? set identity_insert A off
--1. 会话中某个表已将此属性设置为ON,当为另一个表发出了SET IDENTITY_INSERT ON 句时将出错 --测试的表 CREATE TABLE ta(id int IDENTITY(1,1),col int) CREATE TABLE tb(id int IDENTITY(1,1),col int) GO--设置 IDENTITY_INSERT 属性 SET IDENTITY_INSERT ta ON SET IDENTITY_INSERT tb ON GO /*======================================================*/ --2. 如果插入记录的标识值大于表的当前标识值,则SQL Server自动将新插入值作为当前标识值使用 --测试的表 CREATE TABLE tb(id int IDENTITY(1,1),col int)--强制在表中插入标识值 SET IDENTITY_INSERT tb ON INSERT tb(id,col) VALUES(10,1) SET IDENTITY_INSERT tb OFFINSERT tb(col) VALUES(2) SELECT * FROM tb /*--结果 id col ----------------- ----------- 10 1 11 2 --*/ GO /*======================================================*/ --3. 如果插入记录的标识值小于表的当前标识值,则表的当前标识值不受新插入值的影响 --测试的表 CREATE TABLE tb(id int IDENTITY(1,1),col int) INSERT tb VALUES(1) INSERT tb VALUES(2)--强制在表中插入标识值 SET IDENTITY_INSERT tb ON INSERT tb(id,col) VALUES(1,11) SET IDENTITY_INSERT tb OFFINSERT tb(col) VALUES(3) SELECT * FROM tb /*--结果 id col ----------------- ----------- 1 1 2 2 1 11 3 3 --*/
--创建测试表 CREATE TABLE t1(ID int IDENTITY,A int) GO --插入记录 INSERT t1 VALUES(1) GO--1. 将IDENTITY(标识)列变为普通列 ALTER TABLE t1 ADD ID_temp int GOUPDATE t1 SET ID_temp=ID ALTER TABLE t1 DROP COLUMN ID EXEC sp_rename N't1.ID_temp',N'ID',N'COLUMN' INSERT t1 VALUES(100,9) GO--2. 将普通列变为标识列 CREATE TABLE t1_temp(ID int,A int IDENTITY) SET IDENTITY_INSERT t1_temp ON INSERT t1_temp(ID,A) SELECT * FROM t1 SET IDENTITY_INSERT t1_temp OFF DROP TABLE T1 GOEXEC sp_rename N't1_temp',N't1' INSERT t1 VALUES(109999) GO--显示处理结果 SELECT * FROM t1 /*--结果: ID A ----------------- ----------- 1 1 100 9 109999 10 --*/
SET IDENTITY_INSERT on SET IDENTITY_INSERT off
SET IDENTITY_INSERT on insert into ..... SET IDENTITY_INSERT off
--创建测试表 CREATE TABLE t1(ID int IDENTITY,A int) GO --插入记录 INSERT t1 VALUES(1) GO--1. 将IDENTITY(标识)列变为普通列 ALTER TABLE t1 ADD ID_temp int GOUPDATE t1 SET ID_temp=ID ALTER TABLE t1 DROP COLUMN ID EXEC sp_rename N't1.ID_temp',N'ID',N'COLUMN' INSERT t1 VALUES(100,9) GO--2. 将普通列变为标识列 CREATE TABLE t1_temp(ID int,A int IDENTITY) SET IDENTITY_INSERT t1_temp ON INSERT t1_temp(ID,A) SELECT * FROM t1 SET IDENTITY_INSERT t1_temp OFF DROP TABLE T1 GOEXEC sp_rename N't1_temp',N't1' INSERT t1 VALUES(109999) GO--显示处理结果 SELECT * FROM t1 /*--结果: ID A ----------------- ----------- 1 1 100 9 109999 10 --*/引用的5楼
create table ta(id int identity(1,1),name varchar(20)) insert into ta(name) values('abc') insert into ta(name) values('def') insert into ta(name) values('ghi') insert into ta(name) values('jkl') insert into ta(name) values('mnn') alter table ta add id1 int null update ta set id1=id alter table ta drop column id exec sp_rename 'ta.id1','id' insert into ta(name) values('ghi') insert into ta(name) values('jkl') insert into ta(name) values('mnn') select * from ta go drop table ta
允许将显式值插入表的标识列中。语法
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'
GOSELECT *
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').
GOSELECT *
FROM products
GO
-- Drop products table.
DROP TABLE products
GO
insert A(ID,Name) select * from ?
set identity_insert A off
--测试的表
CREATE TABLE ta(id int IDENTITY(1,1),col int)
CREATE TABLE tb(id int IDENTITY(1,1),col int)
GO--设置 IDENTITY_INSERT 属性
SET IDENTITY_INSERT ta ON
SET IDENTITY_INSERT tb ON
GO
/*======================================================*/
--2. 如果插入记录的标识值大于表的当前标识值,则SQL Server自动将新插入值作为当前标识值使用
--测试的表
CREATE TABLE tb(id int IDENTITY(1,1),col int)--强制在表中插入标识值
SET IDENTITY_INSERT tb ON
INSERT tb(id,col) VALUES(10,1)
SET IDENTITY_INSERT tb OFFINSERT tb(col) VALUES(2)
SELECT * FROM tb
/*--结果
id col
----------------- -----------
10 1
11 2
--*/
GO
/*======================================================*/
--3. 如果插入记录的标识值小于表的当前标识值,则表的当前标识值不受新插入值的影响
--测试的表
CREATE TABLE tb(id int IDENTITY(1,1),col int)
INSERT tb VALUES(1)
INSERT tb VALUES(2)--强制在表中插入标识值
SET IDENTITY_INSERT tb ON
INSERT tb(id,col) VALUES(1,11)
SET IDENTITY_INSERT tb OFFINSERT tb(col) VALUES(3)
SELECT * FROM tb
/*--结果
id col
----------------- -----------
1 1
2 2
1 11
3 3
--*/
CREATE TABLE t1(ID int IDENTITY,A int)
GO
--插入记录
INSERT t1 VALUES(1)
GO--1. 将IDENTITY(标识)列变为普通列
ALTER TABLE t1 ADD ID_temp int
GOUPDATE t1 SET ID_temp=ID
ALTER TABLE t1 DROP COLUMN ID
EXEC sp_rename N't1.ID_temp',N'ID',N'COLUMN'
INSERT t1 VALUES(100,9)
GO--2. 将普通列变为标识列
CREATE TABLE t1_temp(ID int,A int IDENTITY)
SET IDENTITY_INSERT t1_temp ON
INSERT t1_temp(ID,A) SELECT * FROM t1
SET IDENTITY_INSERT t1_temp OFF
DROP TABLE T1
GOEXEC sp_rename N't1_temp',N't1'
INSERT t1 VALUES(109999)
GO--显示处理结果
SELECT * FROM t1
/*--结果:
ID A
----------------- -----------
1 1
100 9
109999 10
--*/
SET IDENTITY_INSERT off
insert into .....
SET IDENTITY_INSERT off
CREATE TABLE t1(ID int IDENTITY,A int)
GO
--插入记录
INSERT t1 VALUES(1)
GO--1. 将IDENTITY(标识)列变为普通列
ALTER TABLE t1 ADD ID_temp int
GOUPDATE t1 SET ID_temp=ID
ALTER TABLE t1 DROP COLUMN ID
EXEC sp_rename N't1.ID_temp',N'ID',N'COLUMN'
INSERT t1 VALUES(100,9)
GO--2. 将普通列变为标识列
CREATE TABLE t1_temp(ID int,A int IDENTITY)
SET IDENTITY_INSERT t1_temp ON
INSERT t1_temp(ID,A) SELECT * FROM t1
SET IDENTITY_INSERT t1_temp OFF
DROP TABLE T1
GOEXEC sp_rename N't1_temp',N't1'
INSERT t1 VALUES(109999)
GO--显示处理结果
SELECT * FROM t1
/*--结果:
ID A
----------------- -----------
1 1
100 9
109999 10
--*/引用的5楼
如果你要将自增列改为普通列,再由普通列改为自增列,sql语句不能实现
insert into ta(name) values('abc')
insert into ta(name) values('def')
insert into ta(name) values('ghi')
insert into ta(name) values('jkl')
insert into ta(name) values('mnn')
alter table ta add id1 int null
update ta set id1=id
alter table ta drop column id
exec sp_rename 'ta.id1','id'
insert into ta(name) values('ghi')
insert into ta(name) values('jkl')
insert into ta(name) values('mnn')
select * from ta
go
drop table ta