在 SQL Server 2008 R2 中更新 MERGE 语句中插入的记录

在 SQL Server 2008R2 中，您尝试做的事情不能在一个步骤中完成.更新 ParentId 必须是第二步，因为您无法访问一行中的 OUTPUT 值，而该行是另一行插入的结果.但是，您已经在为第二步收集信息.所以，您只需要添加一个简单的更新.

What you are trying to do cannot be done in a single step in SQL Server 2008R2. Updating the ParentId has to be a second step, as you cannot access OUTPUT values in one row that where the result of the insert of another row. However, you are already collecting the information for the second step. So, you just need to add a simple update.

IF OBJECT_ID('dbo.Person') IS NOT NULL DROP TABLE dbo.Person;
IF OBJECT_ID('dbo.Family') IS NOT NULL DROP TABLE dbo.Family;

CREATE TABLE dbo.Family(FamilyID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, FamilyName NVARCHAR(60));
CREATE TABLE dbo.Person(PersonID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, FamilyID INT REFERENCES dbo.Family(FamilyID), PersonName NVARCHAR(60), ParentID INT);
INSERT INTO dbo.Family(FamilyName) VALUES
('DA1'),
('DA2');

INSERT INTO dbo.Person(FamilyID, PersonName, ParentID) VALUES
(1, 'ABC', NULL),
(1, 'Son of ABC', 1),
(1, 'Son of ABC', 1),
(2, 'XYZ', NULL),
(2, 'Son of XYZ', 4 );

DECLARE @FamilyIdMap table (TargetId int, SourceId int)
MERGE dbo.Family tf
USING (SELECT * FROM dbo.Family WHERE FamilyName like '%DA%') AS sf
ON 1=0
WHEN NOT MATCHED THEN
INSERT (FamilyName)
VALUES(sf.FamilyName)
OUTPUT INSERTED.FamilyID, sf.FamilyID
INTO @FamilyIdMap;

DECLARE @PersonIdMap table (TargetId int, SourceId int)

MERGE dbo.Person as tp
USING (SELECT p.PersonID, p.FamilyID, p.PersonName, p.ParentID, fm.SourceId,fm.TargetId FROM Person AS p
INNER JOIN @FamilyIdMap AS fm 
ON p.FamilyID = fm.SourceId) AS sp
ON (0=1)
WHEN NOT MATCHED THEN
INSERT(FamilyID, PersonName, ParentID)
VALUES
(sp.TargetId,PersonName, ParentID) OUTPUT
INSERTED.PersonID, sp.PersonID
INTO @PersonIdMap;

UPDATE p SET
  ParentID = pm.TargetId
FROM dbo.Person AS p
JOIN @PersonIdMap pm
ON pm.SourceId = p.ParentID
WHERE EXISTS(SELECT 1 FROM @PersonIdMap pmf WHERE pmf.TargetId = p.PersonID);

SELECT * FROM dbo.Family;
SELECT * FROM @FamilyIdMap;
SELECT * FROM dbo.Person;
SELECT * FROM @PersonIdMap;

我确实添加了代码来创建和填充@FamilyIdMap 表.我也清理了你原来的 MERGE 一点.它现在使用 @FamilyIdMap 表作为选择行而不是再次加入 dbo.Family 表的方法.如果您只在一小部分家庭上运行它，这应该会更快.如果您有很多家庭并且您将它们全部复制，则再次反对 dbo.Family 表可能会更快.

I did add code to create and fill the @FamilyIdMap table. I also cleaned up your original MERGE a little. It is now using the @FamilyIdMap table as a means to select the rows instead of joining to the dbo.Family table again. If you run this only on a small subset of families this should be faster. If you have a lot of families and you copy them all, going against the dbo.Family table again might be faster.

最后的 UPDATE 只更新 Person 表中的新行(所有新创建的 PersonId 都可以在 @PersonIdMap 表的 TargetId 列中找到)，使用@PersonIdMap 表中的信息将旧的 ParentId 值更改为新的 ParentId 值.

The final UPDATE updates only new rows in the Person table (all newly created PersonIds can be found in the TargetId column of the @PersonIdMap table), changing old ParentId values to new ParentId values using the information in the @PersonIdMap table.

我没有包括事务管理，但至少 MERGE dbo.Person 和下面的 UPDATE dbo.Person 应该在同一个事务中执行.

I did not include transaction management, but atleast the MERGE dbo.Person and the following UPDATE dbo.Person should be executed inside the same transaction.