3

I am trying to match rows from a table in source and destination db tables. I am able to delete that row which is present in dest table and not present in source table. However I also need to delete its dependent rows in other tables inside the dest db. Is there a way I can achieve this inside a merge.

Merge statement goes like this below.

MERGE Table1 as [Target]

using (select  A,B,C,D,E,
F,G,H,I,J,K,L
from Source.dbo.Table1 as al
where al.H is null) AS [Source]

ON ([Target].A = [Source].B)

WHEN NOT MATCHED By TARGET THEN
    INSERT(C,D,E,F,G)
    VALUES([Source].C,[Source].D,[Source].E,[Source].F,[Source].G);
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

See illustration below.

 Source
DB0.dbo.Table1

ID  IDID    NAME
1   6   xyz
2   3   yzx
3   4   abc
4   5   lym
5   1   tes


Dest
 Table1
ID  IDID    Name
1   6   xyz
2   3   yzx
3   4   abc
4   5   lym
5   1   tes
6   2   ads

Dependent Table
 DB1.Dbo,Table2
IDID    VALUE
1   BST
2   PL
3   NO
4   SS
5   DR
6   CR
7   LM

Thanks in advance.

gaganHR
  • 337
  • 2
  • 7
  • 19
  • Please post the `MEREGE` statement that you tried. – Mahmoud Gamal May 22 '13 at 07:32
  • 1
    You can (should) use `ON DELETE CASCADE` option on your foreign keys in dest db to handle dependent rows. – Nenad Zivkovic May 22 '13 at 07:46
  • Thanks, Nenad Zivkovic, I am capturing values from my select into a temp table and then writing separate delete scripts, I cant update tables with delete trigger on cascade because then I have to regenerate my dbml... I have a lot of tables to update. But yes i agree ON DELETE CASCADE can work well, I will look to re work after the peak period. Thanks – gaganHR May 22 '13 at 11:17

1 Answers1

3

Use a cascading deletion as Nenad Zivkovic suggested.

Here are a couple relevant links that provide some potential answers:

How to delete from source using merge command

Delete rows from multiple tables using a single sql query

Community
  • 1
  • 1
Tyler Morrow
  • 949
  • 8
  • 31
  • Thanks, tjm6f4, I am capturing values from my select into a temp table and then writing separate delete scripts, I cant update tables with delete trigger on cascade because then I have to regenerate my dbml... I have a lot of tables to update. But yes i agree ON DELETE CASCADE can work well, I will look to re work after the peak period. Thanks – gaganHR May 22 '13 at 11:19
  • No problem! Good luck to you! – Tyler Morrow May 23 '13 at 06:01