是否有一种方法可以在INSERT期间了解和引用标识?

Question

考虑以下内容:

CREATE TABLE A
(
    Id int IDENTITY(1,1) NOT NULL,
    Id2 int NULL,
    Name varchar(50)
)
CREATE TABLE B
(
    Id int NULL,
    Name varchar(50)
)
INSERT INTO B (Id, Name)
VALUES
(100, 'val1'),
(200, 'val2'),
(NULL, 'val3'),
(NULL, 'val4')
INSERT INTO A
    (Id2, Name)
SELECT Id, Name FROM B
UPDATE A SET Id2 = Id + 100000 WHERE Id2 IS NULL

是否有一种方法可以避免INSERT之后的UPDATE ?也就是说，是否有一种方法可以知道标识值是什么，并在插入期间引用它?

Answer 1

您可以使用OUTPUT子句来查找已添加到表中的标识值:

DECLARE @newkeys TABLE (PrimaryKeyValue int)
INSERT INTO A 
(
Name, Id2
)
OUTPUT INSERTED.IDENTITYCOL INTO @newkeys
SELECT Name, Id FROM B

但是您仍然需要更新表作为第二步—在INSERT批处理完成之前，标识值是不可用的。

Answer 2

您可以使用instead of insert触发器

create trigger trg_test on A instead of insert
as begin
  INSERT INTO A (name,id2)
  SELECT INSERTED.name,
  case when INSERTED.id2 is null then 10000 else INSERTED.id2 end 
  as super_id from INSERTED;
end

您也可以使用AFTER INSERT触发器和UPDATE记录，如

create trigger trg_test on A after insert
as begin
    UPDATE a 
    SET id2 = i.Id + 100000 
    FROM A a
    INNER JOIN INSERTED i 
    ON (i.Id = a.Id)
    WHERE a.id2 is null;
end

所以每个记录插入触发器将触发并更新记录。因此，在Insert查询完成后，结果将如下所示。查看更多关于触发器的信息

1   100     val1
2   200     val2
3   100003  val3
4   100004  val4

另一种没有任何触发器的方法，使用如下所示的临时表。

CREATE TABLE #temp1
(
    Id int IDENTITY(1,1) NOT NULL, 
    Id2 int NULL,
    Name varchar(50)
);
insert into #temp1(id2,name)
select id,name from B

UPDATE a 
SET id2 = i.Id + 100000 
FROM A a
INNER JOIN #temp1 i 
ON (i.Id = a.Id)
WHERE a.id2 is null;