时间戳

首页 > 解决方案 > 如何处理 SQL 插入中未包含的输出值

sql-server - 如何处理 SQL 插入中未包含的输出值

问题描述

我正在编写一个导入过程，它将数据从一个（有些旧的）数据库导入另一个。导入过程采用一张包含源数据的平面表。我#SourcePersonAccount在开始时填充了一个临时表（）。目标是将此数据分布到三个目标表（dbo.Person、dbo.Account& dbo.PersonAccount）中。这在使用 SQL Server 复制的表上的触发器内运行，因此需要快速运行。

#SourcePersonAccount([AccountNumber], [CompanyId], [TargetPersonId], [TargetAccountId]);
dbo.Person ([Id] pk identity(1,1), [CompanyId], ...);
dbo.Account ([Id] pk identity(1,1), [AccountNumber], ...);
dbo.PersonAccount ([Id], [PersonId] fk_Person_Id, [AccountId] fk_Account_Id);

在我的代码中，我已经在临时表中TargetPersonId填充了。#SourcePersonAccount剩下的就是 1) insert into dbo.Account，2)#SourcePersonAccount使用插入的dbo.Account.Id值更新，3) insert into dbo.PersonAccount。

挑战之一是AccountNumber和CompanyId构成源表的复合主键，因此需要两者才能在#SourcePersonAccount临时表上正确连接。

我在这里和这里看到了在一定程度上解决类似问题的线程，这并没有解决我的特定问题，主要是由于性能问题。

如本文所述，该子句OUTPUT无法输出插入中未包含的列，因此此处不是一个选项。

我看到的一种解决方案在技术上可以提供所需的输出（我找不到找到建议的链接），同时使用该OUTPUT子句实际上是在查询中添加和删除一列。

DECLARE @PersonAccountTbl TABLE ([AccountId] INT, [AccountNumber] INT, [CompanyId] INT);

ALTER TABLE [dbo].[Account]
    ADD [CompanyId] INT NULL;

INSERT INTO [dbo].[Account]
    ([AccountNumber], [CompanyId])
OUTPUT INSERTED.[Id], INSERTED.[AccountNumber], INSERTED.[CompanyId]
INTO @PersonAccountTbl
SELECT
    [AccountNumber], [CompanyId]
FROM #SourcePersonAccount
WHERE
    [TargetAccountId] IS NULL;

ALTER TABLE [dbo].[Account]
    DROP COLUMN [CompanyId];

对于我的情况，这不是一个可行的选择。

我尝试使用MERGE我在这个问题上发现的每个线程都建议使用它。我不喜欢MERGE有几个原因。反正我试过了；下面的代码给出了所需的输出，但最终对于我的目的来说太慢了。

DECLARE @PersonAccountTbl TABLE ([AccountId] INT, [AccountNumber] INT, [CompanyId] INT);

MERGE INTO [dbo].[Account] a
USING #SourcePersonAccount spa
    ON spa.[TargetAccountId] IS NULL
WHEN NOT MATCHED THEN
    INSERT
        ([AccountNumber])
    VALUES
        (spa.[AccountNumber])
    OUTPUT INSERTED.[Id], INSERTED.[AccountNumber], spa.[CompanyId]
    INTO @PersonAccountTbl ([AccountId], [AccountNumber], [CompanyId]);

UPDATE spa
    SET spa.[TargetAccountId] = pat.[AccountId]
FROM #SourcePersonAccount spa
JOIN @PersonAccountTbl pat
    ON pat.[AccountNumber] = spa.[AccountNumber]
    AND pat.[CompanyId] = spa.[CompanyId];

INSERT INTO [dbo].[PersonAccount]
    ([PersonId], [AccountId])
SELECT
    spa.[TargetPersonId], spa.[TargetAccountId]
FROM #SourcePersonAccount spa
LEFT JOIN [dbo].[PersonAccount] pa
    ON pa.[PersonId] = spa.[TargetPersonId]
    AND pa.[AccountId] = spa.[TargetAccountId]
WHERE
    pa.[Id] IS NULL;

除了添加/删除列之外，还有其他方法可以完成此操作吗？ MERGE

标签： sql-servertsql

解决方案

您可以使用SEQUENCE代替 IDENTITY 列。然后，您可以在插入数据之前将 ID 分配给临时表或表变量。

推荐阅读