Início / dba / Perguntas / 325460
Accepted
Ian Kemp
Asked: 2023-04-01 00:17:26 +0800 CST

É possível atualizar uma tabela existente em uma única instrução, usando a saída de uma instrução MERGE?

  • 772

Sei que posso conseguir isso de várias maneiras - essa é uma questão puramente acadêmica para ver se é possível fazer isso em uma única instrução, a fim de aprofundar meus conhecimentos de SQL.

Estamos dividindo uma tabela antiga e larga em duas tabelas mais estreitas, uma pai e uma filha:

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 ) )

WorkTablecontém vários registros da tabela original - o ID dessa tabela que queremos usar como o ID na tabela filho (via identity_insert) e um campo dessa tabela que realmente queremos definir no pai. Para cada linha em WorkTable, portanto, teremos 1 linha em ParentTablee outra em ChildTable.

Agora vou preencher ParentTablee também obter os IDs de seus registros recém-inserido para a inserção subsequente em 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

Isso funciona, mas é feio. Eu preferiria muito mais se pudesse simplificá-lo em uma instrução, em que os IDs inseridos de ParentTablesão atualizados diretamente de volta Worktable- removendo assim a necessidade da @IdsMaptabela var que existe apenas para realizar essa atualização.

Achei que poderia conseguir isso usando o mergeDML aninhado:

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

mas o MSSQL diz que não:

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

O DML aninhado dentro de um CTE também falha:

;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.

Existe alguma maneira de conseguir o que estou procurando?

sql-server

2 respostas

  1. Best Answer

    Não com um relacionamento de chave estrangeira verificado, não. O DML aninhado não funciona quando a tabela está em um dos lados de um relacionamento FK imposto. Observe também que apenas um exterior INSERTé permitido.

    Não verificar temporariamente o FK quase funcionaria, mas a identidade na tabela filho apresenta outro obstáculo. A inserção de DML aninhada não é permitida para usar a inserção de identidade.

    Mesmo desabilitar e restabelecer a chave estrangeira frustraria seu desejo de implementar a operação em uma única instrução. Dada essa exigência, acho que a resposta à sua pergunta é não.

    Exemplo de quase funcionando (sem a identidade da criança):

    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');
    ParentId ChildId ParentField1
    1 10 PF1
    2 20 PF2
    3 30 PF3

    Solução:

    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;

    plano de execução

    O SQL dinâmico é necessário para evitar um erro devido à restrição imposta no momento da compilação em lote.

    Pai:

    Eu ia Campo1
    1 PF1
    2 PF2
    3 PF3

    Criança:

    Eu ia ParentId
    10 1
    20 2
    30 3

    Quando a tabela filho tem uma coluna de identidade, o erro é:

    Msg 5328, Nível 16, Estado 1
    Não é possível inserir valor explícito para a coluna de identidade 'Id' na tabela de destino 'dbo.ChildTable' da instrução INSERT quando a cláusula FROM contém uma instrução INSERT, UPDATE, DELETE ou MERGE aninhada.

    • 3

  2. Por que você quer que seja uma consulta complicada?

    A tarefa que você descreve pode ser facilmente resolvida por um loop simples:

    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

    E claro, dessa forma você pode fazer quantas manipulações e conversões de dados forem necessárias, entre a leitura da tabela de origem e a gravação nos destinos.

    Além disso, você terá fácil leitura e modificação do script.

    • -4

relate perguntas