SQL Express:尝试插入具有自动递增的PK字段的表中. [英] SQL Express: Trying to insert into a table with an auto-incremented PK field.
问题描述
我试图从一个表中选择一行并将其插入到另一个表中，当表中的第一列是主键时，会自动递增.
(它们实际上是2个不同DB上的同一张表，我正在尝试为...创建触发器).
我自然尝试选择除自动递增的列以外的所有列，并插入它们，以便自动递增的列将自动填充，
(它们在两个DB上不必具有相同的值，但是我确定触发器是否正确运行，它们可能会...)
通过执行:
Hi,
I am trying to select a row from one table and insert it into another, when the 1st column in the tables is a primary key, auto-incremented.
(They are actually the same table on 2 different DBs, which I''m trying to create a trigger for...)
I naturally tried to select all colums but the auto-incremented one, and insert those, so that the auto incremented column will be filled automaticly,
(They do not have to be with the same value on the two DBs, but I figgured if the triggers works right they probably will be...)
by doing:
INSERT INTO DB1.Table1
(Name, Phone, FAX, Address)
SELECT
max.Name, max.Phone, max.FAX, max.Address
FROM DB2.Table1 as max
WHERE max.Id = (SELECT MAX(Id) FROM DB2.Table1)
意思是我选择并插入了所有列，但第一个(Id)却是有问题的...
最后的条件已经完成，以确保我添加了最后一个...
这不起作用，因此我也尝试添加:
Meaning that I selected and inserted all the columns BUT the 1st one (Id) which is the problematic one...
And the condition in the end is done to make sure that I get the last one added...
This does not work, so I also tried adding:
SET IDENTITY_INSERT Table1 ON
GO
我在某处读到的东西...
在这两种情况下，我都会得到:
that I read about somewhere...
In both cases I get:
消息545，级别16，状态1，第2行
当IDENTITY_INSERT设置为ON或将复制用户插入到NOT FOR REPLICATION标识列中时，必须为表"Table1"中的标识列指定显式值.
Msg 545, Level 16, State 1, Line 2
Explicit value must be specified for identity column in table ''Table1'' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column.
有什么想法吗?
Ron.
Any ideas?
Ron.
推荐答案
IDENTITY列INSERT的缩略图规则:
默认情况下，SQL Server不允许您在IDENTITY列中插入一个值.您需要显式启用IDENTITY INSERT.
每个会话一次只能有一个TABLE可以将IDENTITY INSERT一次设置为ON.
您无法更新IDENTITY列.如果您尝试这样做，SQL Server将生成一个错误
Thumb rule for IDENTITY column INSERT:
By default SQL Server does not allow you to insert a value into an IDENTITY column. You need to explicitly enable IDENTITY INSERT.
Only one TABLE per session can have IDENTITY INSERT set to ON at a time.
You cannot update an IDENTITY column. SQL Server will generate an error if you attempt to do that
这篇关于SQL Express:尝试插入具有自动递增的PK字段的表中.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
