CODEWITHSUNDEEP
sqlsql-servert-sqlmerge
disasterkid
disasterkid

Reputation: 7278

Merge statement with a conditional INSERT

I noticed in my merge statement that there are rows in the source table with a PackageId of 0. Those records should not be inserted into the destination table. So I'd like to have this condition on my INSERT.

MERGE MyTargetTable AS TARGET
USING MySourceTable AS SOURCE
ON (SOURCE.orderidHash = TARGET.Id)
WHEN MATCHED AND (TARGET.CustomerId <> SOURCE.CustomerId
    OR TARGET.PackageId <> SOURCE.packageid
    OR TARGET.TypeId <> SOURCE.ordertypeid
    OR TARGET.Created <> SOURCE.Created
    OR TARGET.Updated <> SOURCE.Changed)
THEN
UPDATE SET TARGET.CustomerId = SOURCE.customeridHash,
     TARGET.PackageId = SOURCE.packageid,
     TARGET.TypeId = SOURCE.ordertypeid,
     TARGET.Created = SOURCE.Created,
     TARGET.Updated = SOURCE.Changed
WHEN NOT MATCHED BY TARGET 
INSERT (
    Id, 
    CustomerId, 
    PackageId,
    TypeId,
    Created, 
    Updated
)
VALUES (
    SOURCE.orderidHash,
    SOURCE.customeridHash,
    SOURCE.packageid,
    SOURCE.ordertypeid,
    SOURCE.Created,
    SOURCE.changed
)
WHEN NOT MATCHED BY SOURCE THEN DELETE;

Where in the above statement should I have WHERE PackageId <> 0? When I put the condition after WHEN NOT MATCHED BY TARGET I get the error message:

The MERGE statement conflicted with the FOREIGN KEY constraint "FK_MyTargetTable_DimCustomer". The conflict occurred in database MyDatabase, table DimCustomer, column Id.

MyTargetTable.CustomerId is a FK to DimCustomer.Id.

Upvotes: 2

Views: 716

Answers (1)

benjamin moskovits
benjamin moskovits

Reputation: 5458

just change the 

USING MySourceTable AS SOURCE
to
using (select * from MySourceTable where  PackageId <> 0) as Source

Upvotes: 1

Related Questions