-- Merge target table
CREATE TABLE #Target
(
pk integer NOT NULL,
ak character(1) NOT NULL,
status_code character(1) NOT NULL,
PRIMARY KEY (pk)
);
-- Filtered unique index
CREATE UNIQUE INDEX uq1
ON #Target (ak)
INCLUDE (status_code)
WHERE status_code = 'a';
-- Changes to apply
CREATE TABLE #Changes
(
pk integer NOT NULL,
status_code character(1) NOT NULL,
PRIMARY KEY (pk)
);
-- Sample data
INSERT #Target
(pk, ak, status_code)
VALUES
(1, 'A', 'a'),
(2, 'B', 'a'),
(3, 'C', 'a'),
(4, 'A', 'd');
-- Sample changes
INSERT #Changes
(pk, status_code)
VALUES
(1, 'd'),
(4, 'a');
-- Succeeds
MERGE #Target AS t
USING #Changes AS c ON
c.pk = t.pk
WHEN MATCHED
AND c.status_code <> t.status_code
THEN UPDATE SET status_code = c.status_code;
-- Reverse the changes
TRUNCATE TABLE #Changes;
INSERT #Changes
(pk, status_code)
VALUES
(1, 'a'),
(4, 'd');
-- Fails with unique key violation
MERGE #Target AS t
USING #Changes AS c ON
c.pk = t.pk
WHEN MATCHED
AND c.status_code <> t.status_code
THEN UPDATE SET status_code = c.status_code;
-- Equivalent UPDATE succeeds
BEGIN TRANSACTION;
UPDATE t
SET status_code = c.status_code
FROM #Target AS t
JOIN #Changes AS c ON
t.pk = c.pk
WHERE
c.status_code <> t.status_code;
-- Reset to test MERGE again
ROLLBACK TRANSACTION;
-- Merge succeeds with a *wide update plan*
-- Split/Filter/Sort/Collapse needed!
MERGE #Target AS t
USING #Changes AS c ON
c.pk = t.pk
WHEN MATCHED
AND c.status_code <> t.status_code
THEN UPDATE SET status_code = c.status_code
OPTION (QUERYTRACEON 8790);
-- Tidy up
DROP TABLE #Changes, #Target;
Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'dbo.#Target' with unique index 'uq1'. The duplicate key value is (A).
The statement has been terminated.