Qual abordagem para upserts seguros de simultaneidade é melhor para entradas com valor de tabela se MERGE for proibido?

Faça a atualização primeiro e depois faça uma inserção com uma where not existscláusula. Não é possível testar @@rowcountporque algumas linhas podem ter sido atualizadas.

Se você fizer a inserção primeiro, você também atualizará as linhas que acabou de inserir.

Problemas MERGEgeralmente se manifestam com combinações de recursos (especialmente os relativamente novos) que resultam em planos de alteração de dados altamente complexos. Isso é uma pena, pois MERGEé, sem dúvida, uma maneira conveniente de expressar coisas como um "upsert".

Solução alternativa

Você pode considerar o seguinte:

1. Crie uma visualização sobre a tabela de destino.
2. Crie INSTEAD OFgatilho(s) para visualização INSERTe UPDATEações.
3. Escreva seu 'upsert' como um MERGEcontraponto à visão.

Vantagens:

1. Ele MERGEvê um alvo simples , então o plano de alteração de dados não é complexo.
2. O SQL Server preenche tabelas de trabalho internas separadas para arquivos * inseridos e excluídos .
3. O código do gatilho processa inserções e atualizações simples separadamente.
4. Você não precisa mais de uma transação explícita em torno de instruções de inserção e atualização separadas.

Desvantagens:

1. Os gatilhos acrescentam alguma sobrecarga.
2. Você precisa lembrar de direcionar 'upserts' para a visualização.
3. A OUTPUTcláusula não funcionará para referências inseridas .

Esta solução alternativa permite que você escreva um MERGE, mas tenha as ações resultantes executadas como inserções e atualizações simples separadas.

As precauções usuais para simultaneidade MERGEainda são necessárias.

Usei uma visualização assumindo que você prefere não ter INSTEAD OFgatilhos na tabela base.

_{* INSTEAD OFgatilhos não usam controle de versão de linha. Veja meu artigo, Coisas interessantes sobre gatilhos INSTEAD OF .}

Demonstração

Há melhorias a serem feitas (como a possibilidade de combinar os gatilhos). O código a seguir prioriza a clareza:

db<>violino

Linhas de tabela e amostra

CREATE TABLE dbo.AccountDetails
(
    Email nvarchar(400) NOT NULL 
        CONSTRAINT PK_AccountDetails 
        PRIMARY KEY CLUSTERED,
    Created datetime NOT NULL 
        DEFAULT GETUTCDATE(),
    Etc nvarchar(max) NULL
);

INSERT dbo.AccountDetails
    (Email, Etc)
VALUES
    (N'[email protected]', N'Original 2792'),
    (N'[email protected]', N'Original 3129'),
    (N'[email protected]', N'Original 4726'),
    (N'[email protected]', N'Original 5766');

Visualizar

CREATE VIEW 
    dbo.AccountDetails_Upsert
WITH SCHEMABINDING
AS
SELECT 
    Email, 
    Etc 
FROM dbo.AccountDetails;

INSTEAD OF INSERTgatilho de visualização

CREATE TRIGGER 
    AccountDetails_Upsert_InsteadOfInsert
ON dbo.AccountDetails_Upsert
INSTEAD OF INSERT 
AS
IF  ROWCOUNT_BIG() = 0 RETURN;

SET NOCOUNT, XACT_ABORT ON;
SET ROWCOUNT 0;

IF NOT EXISTS (SELECT * FROM Inserted) RETURN;

-- Simple insert
INSERT dbo.AccountDetails 
    (Email, Etc)
SELECT 
    Email, Etc
FROM Inserted;

INSTEAD OF UPDATEgatilho de visualização

CREATE TRIGGER 
    AccountDetails_Upsert_InsteadOfUpdate
ON dbo.AccountDetails_Upsert
INSTEAD OF UPDATE 
AS
IF  ROWCOUNT_BIG() = 0 RETURN;

SET NOCOUNT, XACT_ABORT ON;
SET ROWCOUNT 0;

IF NOT EXISTS (SELECT * FROM Inserted) RETURN;

-- Simple update
UPDATE AD
SET Etc = I.Etc
FROM Inserted AS I
JOIN dbo.AccountDetails AS AD
    ON AD.Email = I.Email;

Valor de tabelaMERGE

DECLARE @Changes AS table
(
    Email nvarchar(400) NOT NULL PRIMARY KEY,
    Etc nvarchar(max) NULL
);

INSERT @Changes
    (Email, Etc)
VALUES
    -- Update some rows
    (N'[email protected]', N'Updated 2792'),
    (N'[email protected]', N'Updated 3129'),
    (N'[email protected]', N'Updated 4726'),
    (N'[email protected]', N'Updated 5766'),
    -- Insert some rows
    (N'[email protected]', N'Inserted a'),
    (N'[email protected]', N'Inserted b');

MERGE dbo.AccountDetails_Upsert
WITH 
(
    UPDLOCK, 
    SERIALIZABLE,
    FORCESEEK
) AS V
USING @Changes AS C
    ON C.Email = V.Email
WHEN MATCHED THEN 
    UPDATE
    SET Etc = C.Etc
WHEN NOT MATCHED BY TARGET THEN 
    INSERT (Email, Etc) 
    VALUES (C.Email, C.Etc);

Resultados

MERGEplano

Arrumar

DROP VIEW IF EXISTS 
    dbo.AccountDetails_Upsert;

DROP TABLE IF EXISTS 
    dbo.AccountDetails;