SET IDENTITY_INSERT ON的问题

Question

我试图进行如下插入操作：
INSERT INTO Inventory
SELECT * FROM Inventory_t WHERE cInvCode NOT IN (SELECT cInvCode FROM Inventory)
可是，提示错误：
仅当使用了列的列表，并且 IDENTITY_INSERT 为 ON 时，才能在表 'Inventory' 中为标识列指定显式值。于是乎，我将上面的语句改为：
SET IDENTITY_INSERT Inventory ON
go
INSERT INTO Inventory
SELECT * FROM Inventory_t WHERE cInvCode NOT IN (SELECT cInvCode FROM Inventory)
go
SET IDENTITY_INSERT Inventory OFF
go
可是，仍然提示：
仅当使用了列的列表，并且 IDENTITY_INSERT 为 ON 时，才能在表 'Inventory' 中为标识列指定显式值。
问题一：为什么我加上SET IDENTITY_INSERT Inventory ON还不行呢？
问题二：可否在如上的插入语句中，加上一个“参数”，说明插入所有字段，但除了自增量字段Field1？
多谢热心人们

Answer 1

SET IDENTITY_INSERT Inventory ON    
go
INSERT INTO Inventory(字段1,字段2,字段3,....)
SELECT 字段1A,字段2A,字段3A,... FROM Inventory_t WHERE cInvCode NOT IN (SELECT cInvCode FROM Inventory)
go
SET IDENTITY_INSERT Inventory OFF    
go

Answer 2

--近期有很多类似问题出现。
insert into table1
select 字段(不含自增列) from table2

Answer 3

联机丛书：任何时候，一个会话中只有一个表的 IDENTITY_INSERT 属性可以设置为 ON。如果某个表已将此属性设置为 ON，则对另一个表发出 SET IDENTITY_INSERT ON 语句时，SQL Server 2005 将返回一个错误信息，指出 SET IDENTITY_INSERT 已设置为 ON，并报告已将其属性设置为 ON 的表。
参考！

Answer 4

问题一：为什么我加上SET IDENTITY_INSERT Inventory ON还不行呢？SET IDENTITY_INSERT Inventory ON 
设置后必须指定字段名，这个我看你的第二个问题，似乎你已经知道了问题二：可否在如上的插入语句中，加上一个“参数”，说明插入所有字段，但除了自增量字段Field1？
多谢热心人们这个确实没有

Answer 5

set indentity_insert on 后，后续提供的表插入语句需要提供字段的列表和值列表，不能通过默认全字段插入的语法来进行。

Answer 6

问题一：为什么我加上SET IDENTITY_INSERT Inventory ON还不行呢？
--因为要显式指定要插入表的字段名。
问题二：可否在如上的插入语句中，加上一个“参数”，说明插入所有字段，但除了自增量字段Field1？
多谢热心人们
--直接一句是不行的，可以通过动态SQL间接变通实现：
declare @s varchar(8000)
select @s=isnull(@s+',','')+quotename(name)
from syscolumns
where id=object_id('Inventory')
    and name<>'Field1'
EXEC('
INSERT INTO Inventory('+@s+')
SELECT * FROM Inventory_t WHERE cInvCode NOT IN (SELECT cInvCode FROM Inventory)
')

Answer 7

带有自增列属性的表在插入时必须显示指定插入的列的名称。
如果使用SET IDENTITY_INSERT ON   开启自增列插入，则插入列表的列要包含自增列，这时自增列是可写的，插入的值与源值一致。
如果没有使用SET IDENTITY_INSERT ON  ，则插入列表的列不能包含自增列，这时自增列的值是不可写的，由系统自动维护，如果是insert into select 的话可能导致目标表的自增列值与源表自增列的值不一致。
根据实际需要选择何种方式插入。

Answer 8

and name<>'Field1' 这个条件要去掉，否则列的数目不匹配。

Answer 9

仅供参考--SET IDENTITY_INSERT ON  是显示指定所有列包括自增列
--SQL 2000
SET IDENTITY_INSERT Inventory ON 
declare @s varchar(8000)
select @s=isnull(@s+',','')+quotename(name)
from syscolumns --SQL 2000 系统表
where id=object_id('Inventory')
    --and status <> 0x80 --十进制 128 status = 0x80 表示自增列
EXEC('
INSERT INTO Inventory('+@s+')
SELECT ' + @s +' FROM Inventory_t WHERE cInvCode NOT IN (SELECT cInvCode FROM Inventory)
')
SET IDENTITY_INSERT Inventory OFF 
--SQL 2005
SET IDENTITY_INSERT Inventory ON 
declare @s varchar(8000)
select @s=isnull(@s+',','')+quotename(name)
from sys.columns --SQL 2005 以后 系统视图
where object_id=object_id('Inventory')
    --and is_identity = 0 --is_identity = 1 表示自增列
EXEC('INSERT INTO Inventory('+@s+')
SELECT ' + @s +' FROM Inventory_t WHERE cInvCode NOT IN (SELECT cInvCode FROM Inventory)
')
SET IDENTITY_INSERT Inventory OFF --未设置SET IDENTITY_INSERT ON 时，显示指定自增列以外的所有列--SET IDENTITY_INSERT ON  是显示指定所有列包括自增列
--SQL 2000
SET IDENTITY_INSERT Inventory OFF 
declare @s varchar(8000)
select @s=isnull(@s+',','')+quotename(name)
from syscolumns --SQL 2000 系统表
where id=object_id('Inventory')
    and status <> 0x80 --十进制 128 status = 0x80 表示自增列
EXEC('
INSERT INTO Inventory('+@s+')
SELECT ' + @s +' FROM Inventory_t WHERE cInvCode NOT IN (SELECT cInvCode FROM Inventory)
')--SQL 2005
SET IDENTITY_INSERT Inventory OFF 
declare @s varchar(8000)
select @s=isnull(@s+',','')+quotename(name)
from sys.columns --SQL 2005 以后 系统视图
where object_id=object_id('Inventory')
    and is_identity = 0 --is_identity = 1 表示自增列
EXEC('INSERT INTO Inventory('+@s+')
SELECT ' + @s +' FROM Inventory_t WHERE cInvCode NOT IN (SELECT cInvCode FROM Inventory)
')

Answer 10

问题一：因为含有自增列，所以执行失败。
问题二：可以指定列插入，但不插入自增列，就可以正常插入了。
如:inventory有四个字段，其中aa是自增列
INSERT INTO Inventory(bb,cc,dd)
SELECT bb,cc,dd
FROM Inventory_t WHERE cInvCode NOT IN (SELECT cInvCode FROM Inventory)
这样就可以了

Answer 11

以前问过这个问题了，后来不了了之，我最后自己实现的方法还是从sysobjects里面取字段：SELECT [name] FROM syscolumns
WHERE (id = (SELECT id FROM sysobjects
WHERE [name]='表名' and [type]<>56))
ORDER BY colid返回字段再去组合 select 语句。