I’m trying to use MERGE to upsert aggregate data into a collection of tables. These tables each have a set of fields and a set of metrics. For each table some fields are nullable and some are not. No keys are declared on the tables. I’m using temp tables, which match the target tables, to hold the aggregate data, which is then consumed by the MERGE statement. When I run the MERGE statements I receive a “Duplicate MERGE key detected in join” error, which shows that it is treating only the not null columns as a key despite my ON clause including syntax to include the null columns in the match.
Sample pseudo syntax:
CREATE TABLE target (
fieldA varchar not null,
fieldB varchar not null,
fieldC varchar null,
metricA int,
metricB int);
CREATE TABLE temp (
fieldA varchar not null,
fieldB varchar not null,
fieldC varchar null,
metricA int,
metricB int);
MERGE INTO target trg
USING temp tmp ON (
trg.FieldA = tmp.FieldA
AND trg.FieldB = tmp.FieldB
AND ((trg.FieldC IS NULL AND tmp.FieldC IS NULL) OR trg.FieldC = tmp.FieldC))
WHEN MATCHED THE UPDATE
SET trg.MetricA = tmp.MetricA ,
trg.MetricB = tmp.MetricB
WHEN NOT MATCHED THEN INSERT (
FieldA ,
FieldB ,
FieldC ,
MetricA ,
MetricB )
VALUES (
tmp.FieldA ,
tmp.FieldB ,
tmp.FieldC ,
tmp.MetricA ,
tmp.MetricB );
Sign up
Already have an account? Login
Welcome to the Rocket Forum!
Please log in or register:
Employee Login | Registration Member Login | RegistrationEnter your E-mail address. We'll send you an e-mail with instructions to reset your password.
