我被问及是否可以有一个带有"identity"列的ID字段的插入语句，并且如果赋值的值也可以在同一个插入语句中插入到同一记录的另一个字段中。这是否可行（SQL Server 2008r2）？
谢谢。
谢谢。
在同一T-SQL语句中获取IDENTITY值？
4
- Sako73
4
4为什么需要将完全相同的值存储在两个独立的列中？你可以参考以下相关问题/答案：SQL Server插入标识列表。 - Joe Stefanelli
我们希望将值默认为标识值，但允许未来的进程更改该列。 - Sako73
为什么需要在一个语句中执行？应该使用单个存储过程或单个事务来完成。 - daniloquio
你可以尝试给第二列添加一个默认值NULL，然后使用计算列来选择原始值或更新后的值，具体实现可以使用COALESCE函数。这样，你就可以随意更新第二列了。 - HABO
6个回答
9
你实际上无法这样做 - 因为用于IDENTITY列的实际值只有在INSERT完成时才会固定和设置。
但是，你可以使用例如触发器。
CREATE TRIGGER trg_YourTableInsertID ON dbo.YourTable
AFTER INSERT
AS
UPDATE dbo.YourTable
SET dbo.YourTable.OtherID = i.ID
FROM dbo.YourTable t2
INNER JOIN INSERTED i ON i.ID = t2.ID
这将在任何行被插入后立即触发，并将
OtherID列设置为插入行的IDENTITY列的值。但严格来说，它并不在同一语句中 - 它只是在您原始语句之后执行。- marc_s
4
这不正确，如果´identity_insert´关闭，您可以分配值。 - aF.
@aF：是的 - 但这样你基本上关闭了IDENTITY机制，而且你没有读取分配的IDENTITY值 - 你自己设置它... - marc_s
@AF：不是 - 当您关闭IDENTITY机制时，您将无法获取/读取该值。您正在覆盖该机制 - 这不是问题的所在...... - marc_s
好的，你是对的。触发器是实现它的方法 :) 但我认为这个问题还有更多的信息 oO - aF.
1
关于选中的答案：
你实际上不能这样做——因为在INSERT完成时，用于IDENTITY列的实际值只是固定和设置的。
marc_s 我想，你并不完全正确。 是的，他可以！ ))
IDENT_CURRENT()：CREATE TABLE TemporaryTable(
Id int PRIMARY KEY IDENTITY(1,1) NOT NULL,
FkId int NOT NULL
)
ALTER TABLE TemporaryTable
ADD CONSTRAINT [Fk_const] FOREIGN KEY (FkId) REFERENCES [TemporaryTable] ([Id])
INSERT INTO TemporaryTable (FkId) VALUES (IDENT_CURRENT('[TemporaryTable]'))
INSERT INTO TemporaryTable (FkId) VALUES (IDENT_CURRENT('[TemporaryTable]'))
INSERT INTO TemporaryTable (FkId) VALUES (IDENT_CURRENT('[TemporaryTable]'))
INSERT INTO TemporaryTable (FkId) VALUES (IDENT_CURRENT('[TemporaryTable]'))
UPDATE TemporaryTable
SET [FkId] = 3
WHERE Id = 2
SELECT * FROM TemporaryTable
DROP TABLE TemporaryTable
此外，您甚至可以将
IDENT_CURRENT() 用作 DEFAULT CONSTRAINT，并且它可以代替 SCOPE_IDENTITY()。 例如，请尝试以下操作：CREATE TABLE TemporaryTable(
Id int PRIMARY KEY IDENTITY(1,1) NOT NULL,
FkId int NOT NULL DEFAULT IDENT_CURRENT('[TemporaryTable]')
)
ALTER TABLE TemporaryTable
ADD CONSTRAINT [Fk_const] FOREIGN KEY (FkId) REFERENCES [TemporaryTable] ([Id])
INSERT INTO TemporaryTable (FkId) VALUES (DEFAULT)
INSERT INTO TemporaryTable (FkId) VALUES (DEFAULT)
INSERT INTO TemporaryTable (FkId) VALUES (DEFAULT)
INSERT INTO TemporaryTable (FkId) VALUES (DEFAULT)
UPDATE TemporaryTable
SET [FkId] = 3
WHERE Id = 2
SELECT * FROM TemporaryTable
DROP TABLE TemporaryTable
- Jonik
1
1但是在具有并发连接的环境中，可能会出现错误的数据记录，因此这是不可接受的。 - Jonik
1
你可以在表中增加一个计算字段来实现这一点：
DECLARE @QQ TABLE (ID INT IDENTITY(1,1), Computed AS ID PERSISTED, Letter VARCHAR (1))
INSERT INTO @QQ (Letter)
VALUES ('h'),
('e'),
('l'),
('l'),
('o')
SELECT *
FROM @QQ
1 1 h
2 2 e
3 3 l
4 4 l
5 5 o
- Gixonita
1
没有看到注释中提到该列将被未来的进程修改。像这样的计算列是无法修改的，我只是在尝试回答是否可以在同一插入中完成。 :) - Gixonita
0
你可以两者都做。
要插入带有“identity”列的行，您需要set identity_insert off。
请注意，您仍然无法复制值！
您可以在此处查看命令here。之后要注意set identity_insert on。
要创建具有相同记录的表，您只需：
- 创建新列；
- 使用null值或其他内容插入它；
- 在插入后使用标识列的值更新该列。
如果您需要同时插入值，则可以使用@@identity全局变量。它将为您提供最后插入的值。因此，我认为您需要执行@@identity + 1。在这种情况下，它可能会给出错误的值，因为@@identity适用于所有表。因此，如果插入发生在具有标识的另一个表中，则会计数。
另一种解决方案是获取最大id并添加一个:)，然后您就可以获得所需的值！
- aF.
2
@HLGEM 我只是提供可能的解决方案。 - aF.
实际上：除非您在该列上显式地设置了
UNIQUE约束，否则您可以插入重复的值 - 没有问题..... - marc_s0
使用这个简单的代码：`SCOPE_IDENTITY()+1`。
- Michael azzar
0
我知道原帖是很久以前的了。但是，最好的解决方法是使用触发器在记录插入后更新字段，我认为还有更有效的方法。
对于这个问题使用触发器一直让我感到困扰。似乎必须有更好的方法。触发器基本上使每次插入执行2次写操作，(1) 插入，然后 (2) 更新第二个 int。触发器还要重新连接表。这对于大型数据库和大型表格而言，会带来额外的开销。我怀疑随着表格越来越大，这种方法的开销也会增加。也许我对此是错误的。但是，在大表格上，这不是一个好的解决方案。
我编写了一个名为 fn_GetIdent 的函数来解决这个问题。它很简单，但实际上需要一些工作来找出来。我最终偶然发现了这个方法。事实证明，从 INSERT 语句的 SET 值赋值子句中调用 IDENT_CURRENT(@variableTableName) 的函数与直接从 INSERT 语句中调用 IDENT_CURRENT(@variableTableName) 的效果不同。它可以让您获得要插入的新标识值。
有一个注意点。当标识为空（即没有记录的空表）时，它会有一些不同的行为，因为sys.identity_columns.last_value是NULL。所以，您必须稍微不同地处理输入的第一条记录。我在函数中放置了代码来解决这个问题，现在它可以工作了。
这是因为每次调用函数，即使在同一个INSERT语句中，也在函数内部创建了自己的新“范围”（我相信这是正确的解释）。因此，您甚至可以使用此函数在一个INSERT语句中插入多行。如果直接从INSERT语句中调用IDENT_CURRENT(@variableTableName)，它将为所有行分配相同的newID值。这是因为标识在整个INSERT语句完成处理后更新（在同一范围内）。但是，从函数中调用IDENT_CURRENT(@variableTableName)会导致每次插入都更新标识值。但是，这都是在INSERT语句本身的函数调用中完成的。因此，一旦创建了函数，就很容易实现。
这种方法是通过调用一个函数（从INSERT语句中）来实现的，该函数在内部执行一次sys.identity_columns.last_value的读取（以查看它是否为NULL并且记录是否存在），然后调用IDENT_CURRENT(@variableTableName)，最后返回到INSERT语句以插入行。因此，它只需要进行一次小的读取（对于每个插入的行），然后进行一次写入插入操作，这比触发器方法的开销要小。如果您在大型数据库中使用触发器方法来处理所有表格，则可能会非常低效。我还没有对其与触发器的性能分析进行过任何比较。但是，我认为这将更加高效，特别是对于大型表格。
我一直在测试它，似乎在所有情况下都有效。如果有人发现这种方法不起作用或存在任何问题，请提供反馈。如果没有问题，请投票支持它。我认为这是一种更好的方法。
也许因为 COVID-19 被困在家里，结果对某些事情很有成效。感谢微软让我有事可做。有人招聘吗？:) 不开玩笑，有人招聘吗？那好吧，现在我完成了这个项目，接下来该怎么办呢？:) 祝大家平安。
以下是代码。不知道这种方法有没有漏洞，请提供反馈。
IF OBJECT_ID('dbo.fn_GetIdent') IS NOT NULL
DROP FUNCTION dbo.fn_GetIdent;
GO
CREATE FUNCTION dbo.fn_GetIdent(@inTableName AS VARCHAR(MAX))
RETURNS Int
WITH EXECUTE AS CALLER
AS
BEGIN
DECLARE @tableHasIdentity AS Int
DECLARE @tableIdentitySeedValue AS Int
/*Check if the tables identity column is null - a special case*/
SELECT
@tableHasIdentity = CASE identity_columns.last_value WHEN NULL THEN 0 ELSE 1 END,
@tableIdentitySeedValue = CONVERT(int, identity_columns.seed_value)
FROM sys.tables
INNER JOIN sys.identity_columns
ON tables.object_id = identity_columns.object_id
WHERE identity_columns.is_identity = 1
AND tables.type = 'U'
AND tables.name = @inTableName;
DECLARE @ReturnValue AS Int;
SET @ReturnValue = CASE @tableHasIdentity WHEN 0 THEN @tableIdentitySeedValue
ELSE IDENT_CURRENT(@inTableName)
END;
RETURN (@ReturnValue);
END
GO
/* The function above only has to be created the one time to be used in the example below */
DECLARE @TableHasRows AS Bit
DROP TABLE IF EXISTS TestTable
CREATE TABLE TestTable (ID INT IDENTITY(1,1),
New INT,
Letter VARCHAR (1))
INSERT INTO TestTable (New, Letter)
VALUES (dbo.fn_GetIdent('TestTable'), 'H')
INSERT INTO TestTable (New, Letter)
VALUES (dbo.fn_GetIdent('TestTable'), 'e')
INSERT INTO TestTable (New, Letter)
VALUES (dbo.fn_GetIdent('TestTable'), 'l'),
(dbo.fn_GetIdent('TestTable'), 'l'),
(dbo.fn_GetIdent('TestTable'), 'o')
INSERT INTO TestTable (New, Letter)
VALUES (dbo.fn_GetIdent('TestTable'), ' '),
(dbo.fn_GetIdent('TestTable'), 'W'),
(dbo.fn_GetIdent('TestTable'), 'o'),
(dbo.fn_GetIdent('TestTable'), 'r'),
(dbo.fn_GetIdent('TestTable'), 'l'),
(dbo.fn_GetIdent('TestTable'), 'd')
INSERT INTO TestTable (New, Letter)
VALUES (dbo.fn_GetIdent('TestTable'), '!')
SELECT * FROM TestTable
/*
Result
ID New Letter
1 1 H
2 2 e
3 3 l
4 4 l
5 5 o
6 6
7 7 W
8 8 o
9 9 r
10 10 l
11 11 d
12 12 !
*/
- Todd Albers
网页内容由stack overflow 提供, 点击上面的可以查看英文原文，
原文链接
原文链接