I have a target table containing a items that have an IsActive flag, and I am inserting and updating from a source table using a MERGE statement. If something exists in the source table then it's active, and if it doesn't then it's not active. The logic is pretty simple:

  • if it exists in the source and the target the row should have IsActive true
  • if it exists only in the source then a new row should be inserted to the target, with IsActive true
  • if it exists only in the target then IsActive should be set to false.

All very straightforward, except the target table also has a discriminating column SourceId which relates to the source table. So for a given source table, I only want to MERGE against rows with the corresponding SourceId.

(My normalised table contains rows of identical data types from multiple systems - I retrieve the data from those systems individually and thus the need to merge from one source at a time)

Here's an example:

IF OBJECT_ID('tempdb..#target') IS NOT NULL DROP TABLE #target    
IF OBJECT_ID('tempdb..#source') IS NOT NULL DROP TABLE #source

CREATE TABLE #target  ( Id INT, SourceId INT, IsActive BIT )   
INSERT #target VALUES (1, 1, 0)
INSERT #target VALUES (2, 1, 1)
INSERT #target VALUES (3, 2, 1)

CREATE TABLE #source ( Id INT )    
INSERT #source VALUES (1)
INSERT #source VALUES (4)

DECLARE @SourceId INT = 1;    
SELECT * FROM #target

MERGE INTO #target t
USING
(
    SELECT [Id] FROM #source
) AS s
ON t.[Id] = s.[Id] AND t.[SourceId] = @SourceId
WHEN MATCHED THEN UPDATE SET [IsActive] = 1
WHEN NOT MATCHED BY TARGET THEN INSERT VALUES ([Id], @SourceId, 1)
WHEN NOT MATCHED BY SOURCE THEN UPDATE SET [IsActive] = 0;

SELECT * FROM #target

My initial attempt was to include the AND t.[SourceId] = @SourceId in the merge condition, bu

sql-server t-sql merge
Edit
Report