MSSQL. Is it possible to execute bulk MERGE ignoring failures?

Question

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

• Dan Guzman: Have you considered joining the temp table to Categories so that the source query will return only valid categories?
• Cristian E.: Yes, pre-filtering was always an option, before feeding the temp table. However I'm wondering if its possible without?
• SMor: 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.
• Dan Guzman: @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

Answer 1

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