0

I am using Table Value Parameter to merge my records, but on top of that I will also need to delete records from my DB that do not exist in my TVP table. Can anyone please kindly assist. Thanks.

An alternative is to simply delete off all records first than proceed to insert but the quantity of the records can be up to thousands hence performance could be a concern. 

MERGE [Customer_Address] ca --[AS TARGET]
   USING @TvpCustomer_Address tca --[AS SOURCE]
   ON (ca.CustomerId = tca.CustomerId)
   AND(ca.[Address] = tca.[Address])

   WHEN NOT MATCHED THEN
   INSERT (CustomerId, [Address]) Values(tca.CustomerId, tca.[Address]);

   WHEN MATCHED THEN
   -- Update...
k80sg
  • 2,443
  • 11
  • 47
  • 84

1 Answers1

0

There is a third 'WHEN' case that you could use for that:

WHEN NOT MATCHED BY SOURCE

I use that to flag records that have gone missing.

souplex
  • 981
  • 6
  • 16