更新表并插入丢失的记录

看来你在找我 inserted

INSERT INTO Receipts (col1, col2....)
OUTPUT INSERTED.*
INTO @CreatedIds -- TEMP TABLE WHICH HOLDS RECENTLY INERTED DATA INCLUDING Receipt_Id (pk)
SELECT col1, col2.... 
FROM Outgoing 
WHERE Receipt_Id IS NULL

SELECT c.*
FROM @CreatedIds c -- Note this is a table variable that you need to manual create.

更新:

最新答复:

您所需要做的就是创建一个序列,而不是一个只有一列的表。然后用序列号更新传出表。

   --create table Outgoing ( id int Primary Key IDENTITY(1,1),data nvarchar(100), record_id int);
--insert into Outgoing values ('john',NULL),('jane',NULL),('jean',NULL);

create sequence dbo.receipts as int start with 1 increment by 1;

update Outgoing
set record_id= NEXT VALUE FOR dbo.receipts
where record_id is null

select * from Outgoing

See working demo

下面是老答案

ID 这两个表中的列都可以更新 Receipt_Id 基于此列返回到 Outgoing

因此,您的步骤是: 1.插入记录

DECLARE @LastRID bigint 
SELECT @LastRID= MAX(Id) FROM Receipts
INSERT INTO Receipts(<col list>)
SELECT <col list> FROM Outgoing WHERE Receipt_Id IS NULL

CHECKSUM

update O
set O.Receipt_Id=R.Id
From Outgoing  O
Join Receipts R 
on CHECKSUM(o.<col list>)=CHECKSUM(R.<col list>)
and R.Id>@LastRID