0

Suppose we have a simple relationship between two tables (Products and Categories) enter image description here

And they contain following data:

enter image description here

And I want to do a SQL MERGE operation on the dbo.Products table from a temporary table, which has following records:

enter image description here

SQL Merge:

-- Declare Temporary Table to read from
SELECT TOP 0 T.*, [Index]= CAST(0 AS int)  INTO [#ProductsTempSource] 
FROM [Products] AS T 
LEFT JOIN [Products]
ON 1 = 0;

-- Insert Values to temporary table
INSERT INTO [#ProductsTempSource] ([Id],[Name],[CategoryId], [Index])  VALUES (1, 'Pork1', 1, 0)
INSERT INTO [#ProductsTempSource] ([Id],[Name],[CategoryId], [Index])  VALUES (2, 'Beef',  3, 1) -- << Note 3 is missing categoryId

-- Execute SQL MERGE
MERGE INTO [Products]  AS DestinationTable
USING (SELECT TOP 100 * FROM [#ProductsTempSource] ORDER BY [Index]) AS StagingTable 
ON DestinationTable.[Id] = StagingTable.[Id]
WHEN MATCHED     THEN
UPDATE
SET     [CategoryId] = StagingTable.[CategoryId], [Name] = StagingTable.[Name]
WHEN NOT MATCHED THEN
INSERT ( [CategoryId], [Name] )
VALUES ( [CategoryId], [Name] )
OUTPUT
    $action,
    StagingTable.[Index],
    DELETED.[Id] AS [Id_deleted], DELETED.[Name] AS [Name_deleted], DELETED.[CategoryId] AS [CategoryId_deleted],
    INSERTED.[Id] AS [Id_inserted], INSERTED.[Name] AS [Name_inserted], INSERTED.[CategoryId] AS [CategoryId_inserted]
;

If I execute the query as is it will fail because of FK Key constraint:

Msg 547, Level 16, State 0, Line 15 The MERGE statement conflicted with the FOREIGN KEY constraint "FK_Products_Categories_CategoryId". The conflict occurred in database "SqlMergeDemoDb", table "dbo.Categories", column 'Id'. The statement has been terminated.

Problem Statement:

What I want to achieve is avoid ALL-OR-NOTHING approach, and do a MERGE in a "best effort" way and accept partial success of records that can be merged, and collect maybe failures in a separate table, or output them? Is this possible using MERGE statement?

Here is a fiddle with DDL

4
  • 1
    Have you considered joining the temp table to Categories so that the source query will return only valid categories?
    – Dan Guzman
    Commented Dec 31, 2021 at 17:19
  • Yes, pre-filtering was always an option, before feeding the temp table. However I'm wondering if its possible without? Commented Dec 31, 2021 at 18:20
  • A single SQL statement is atomic - it either succeeds or does not. So the short answer is no. Workarounds exist by changing your approach - but you did not ask that question. Generally speaking, ETL is best left to tools that are designed to do exactly what you want.
    – SMor
    Commented Dec 31, 2021 at 18:43
  • @CristianE., I mean USING (SELECT TOP 100 * FROM [#ProductsTempSource] AS t JOIN Categories AS c ON c.CategoryId = t.CategoryId ORDER BY [Index]) AS StagingTable
    – Dan Guzman
    Commented Dec 31, 2021 at 18:59

1 Answer 1

2

You can just join Categories.Id, which means that all rows that do not have a correct category will be excluded.

MERGE INTO [Products]  AS DestinationTable
USING (
    SELECT TOP (100) pts.*
    FROM [#ProductsTempSource] pts
    WHERE pts.CategoryId IN (
        SELECT c.Id FROM dbo.Categories c
    )
-- alternatively
 -- JOIN dbo.Categories c ON c.Id = pts.CategoryId
    ORDER BY [Index]
) AS StagingTable 
ON DestinationTable.[Id] = StagingTable.[Id]
WHEN MATCHED THEN
  UPDATE SET
      [CategoryId] = StagingTable.[CategoryId],
      [Name] = StagingTable.[Name]
WHEN NOT MATCHED THEN
    INSERT ( [CategoryId], [Name] )
    VALUES ( [CategoryId], [Name] )
OUTPUT
    $action,
    StagingTable.[Index],
    DELETED.[Id] AS [Id_deleted], DELETED.[Name] AS [Name_deleted], DELETED.[CategoryId] AS [CategoryId_deleted],
    INSERTED.[Id] AS [Id_inserted], INSERTED.[Name] AS [Name_inserted], INSERTED.[CategoryId] AS [CategoryId_inserted]
;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.