主页 / dba / 问题 / 325460
Accepted
Ian Kemp
Asked: 2023-04-01 00:17:26 +0800 CST

是否可以使用 MERGE 语句的输出在单个语句中更新现有表？

  • 772

我知道我可以通过多种方式实现这一点——这是一个纯粹的学术问题，看看是否可以在一条语句中做到这一点，以加深我对 SQL 的了解。

我们将一个旧的宽表拆分为两个较窄的表，一个父表和一个子表：

create table WorkTable
     ( ParentId     int             null
     , ChildId      int         not null
     , ParentField1 varchar(50) not null )

create table ParentTable
     ( Id     int         not null primary key identity
     , Field1 varchar(50) not null )

create table ChildTable
     ( Id       int not null primary key identity
     , ParentId int not null foreign key references ParentTable ( Id ) )

WorkTable包含原始表中的一堆记录 - 我们要用作子表中 ID 的该表中的 ID（通过identity_insert），以及我们实际想要在父表中设置的该表中的字段。因此，对于 中的每一行WorkTable，我们将在 中得到一行ParentTable，在 中得到另一行ChildTable。

现在我要填充ParentTable，并获取其新插入记录的 ID，以便随后插入ChildTable：

declare @IdsMap table
      ( ParentId int not null
      , ChildId  int not null )

merge ParentTable dst
using (
select ChildId
     , ParentField1
  from WorkTable
) src on 1 = 0
when not matched by target then
insert
     ( Field1 )
values
     ( src.ParentField1 )
output inserted.Id as [ParentId]
     , src.ChildId -- can't do this with a standard INSERT...OUTPUT, hence the use of MERGE
  into @IdsMap
;

update wkt
   set wkt.ParentId = ids.ParentId 
  from WorkTable wkt
           join
       @IdsMap   ids on ids.ChildId = wkt.ChildId

这有效，但它很丑陋。如果我可以将它简化为一条语句，我会更愿意，这样插入的 ID 可以ParentTable直接更新回- 从而消除了对仅用于完成此更新的表 varWorktable的需要。@IdsMap

我想我可以通过使用merge嵌套 DML 来完成此操作：

update wkt
   set wkt.ParentId = cte.ParentId
  from WorkTable wkt
           join
(
merge ParentTable dst
using (
select ChildId
     , ParentField1
  from WorkTable
) src on 1 = 0
when not matched by target then
insert
     ( Field1 )
values
     ( src.ParentField1 )
output inserted.Id as [ParentId]
     , src.ChildId
) cte on cte.ChildId = wkt.ChildId

但 MSSQL 说不：

A nested INSERT, UPDATE, DELETE, or MERGE statement is not
allowed on either side of a JOIN or APPLY operator.

CTE 中的嵌套 DML 同样失败：

;with cte as
(
select *
  from
(
merge ParentTable dst
using (
select ChildId
     , ParentField1
  from WorkTable
) src on 1 = 0
when not matched by target then
insert
     ( Field1 )
values
     ( src.ParentField1 )
output inserted.Id as [ParentId]
     , src.ChildId
) _
)
update wkt
   set wkt.ParentId = cte.ParentId
  from WorkTable wkt
           join
                 cte on cte.ChildId = wkt.ChildId

A nested INSERT, UPDATE, DELETE, or MERGE statement is not allowed in a SELECT
statement that is not the immediate source of rows for an INSERT statement.

有什么办法可以实现我想要的吗？

sql-server

2 个回答

  1. Best Answer

    不与已检查的外键关系，不。当表位于强制 FK 关系的任一侧时，嵌套 DML 不起作用。INSERT另请注意，只允许穿外衣。

    暂时不检查 FK几乎可以工作，但子表上的身份是另一个障碍。从嵌套 DML 插入不允许使用标识插入。

    即使禁用然后恢复外键也会破坏您在单个语句中实现该操作的愿望。鉴于该要求，我认为您的问题的答案是否定的。

    差不多工作的例子（没有孩子身份）：

    CREATE TABLE dbo.WorkTable
(
    ParentId integer NULL,
    ChildId integer NOT NULL,
    ParentField1 varchar(50) NOT NULL
);

CREATE TABLE dbo.ParentTable
(
    Id integer NOT NULL PRIMARY KEY IDENTITY,
    Field1 varchar(50) NOT NULL
);

CREATE TABLE dbo.ChildTable
(
    Id integer NOT NULL PRIMARY KEY /*IDENTITY*/,
    ParentId integer NOT NULL
        CONSTRAINT FK_CT_PT_Id
        FOREIGN KEY REFERENCES dbo.ParentTable (Id)
);

INSERT dbo.WorkTable 
    (ParentId, ChildId, ParentField1) 
VALUES 
    (1, 10, 'PF1'),
    (2, 20, 'PF2'),
    (3, 30, 'PF3');
    父母身份 子编号 父字段 1
    1个 10 PF1
    2个 20 PF2
    3个 30 PF3

    解决方案：

    SET XACT_ABORT ON;

BEGIN TRANSACTION;

    ALTER TABLE dbo.ChildTable 
        NOCHECK CONSTRAINT FK_CT_PT_Id;

    EXECUTE (N'
        INSERT dbo.ChildTable 
            WITH (TABLOCKX)
            (Id, ParentId)
        SELECT
            P.ChildId, Id 
        FROM 
        (
            MERGE dbo.ParentTable AS P
            USING dbo.WorkTable AS WT 
                ON 0 = 1
            WHEN NOT MATCHED BY TARGET THEN
                INSERT (Field1) 
                VALUES (WT.ParentField1)
            OUTPUT 
                WT.ChildId, 
                Inserted.Id
        ) AS P
    ');

    ALTER TABLE dbo.ChildTable 
        WITH CHECK 
        CHECK CONSTRAINT FK_CT_PT_Id;

COMMIT TRANSACTION;

    执行计划

    需要动态SQL来防止批量编译时强制约束导致的错误。

    家长：

    ID 字段1
    1个 PF1
    2个 PF2
    3个 PF3

    孩子：

    ID 父母身份
    10 1个
    20 2个
    30 3个

    当子表有标识列时，报错为：

    消息 5328，级别 16，状态 1
    当 FROM 子句包含嵌套的 INSERT、UPDATE、DELETE 或 MERGE 语句时，无法在 INSERT 语句的目标表“dbo.ChildTable”中插入标识列“Id”的显式值。

    • 3

  2. 为什么你希望它成为一个复杂的查询？

    您描述的任务可以通过一个简单的循环轻松解决：

    declare crsr cursor for
    select ParentId, ChildId, ParentField1 from WorkTable

declare @ParentId     int
declare @ChildId      int
declare @ParentField1 varchar(50)

open crsr
fetch next from crsr into @ParentId, @ChildId, @ParentField1

while @@fetch_status = 0
begin
   insert into ParentTable values(@ParentId, @ParentField1)
   insert into ChildTable values(@ChildID, @ParentId)

   fetch next from crsr into @ParentId, @ChildId, @ParentField1
end

close crsr
deallocate crsr

    当然，通过这种方式，您可以根据需要在从源表读取和写入目标之间进行尽可能多的数据操作和转换。

    此外，您将可以轻松阅读和修改脚本。

    • -4

相关问题