36

What is the difference between NOT MATCHED BY SOURCE vs NOT MATCHED BY TARGET?

For example:

Does WHEN NOT MATCHED BY SOURCE mean that the records exist in the target but not in the source? - so we can delete them ?

and WHEN NOT MATCHED BY TARGET - mean the records exists in the source but not in the target? so we can insert them?

ΩmegaMan
  • 29,542
  • 12
  • 100
  • 122
TResponse
  • 3,940
  • 7
  • 43
  • 63
  • 1
    That is what they mean, but what you want to do when they aren't matched entirely depends on what you want to achieve. e.g. does a record existing in your target but not in your source (not matched by source) mean you don't want it in the target table? – ZLK Sep 21 '16 at 04:17
  • basically if MATCHED AND [TARGET].IS_DELETED = 1 THEN mark them as un-deleted or WHEN NOT MATCHED BY TARGET (but they are in the source) then insert them or WHEN NOT MATCHED BY SOURCE AND [TARGET].[IS_DELETED] = 0 mark them as deleted - make sense? – TResponse Sep 21 '16 at 04:33
  • 2
    Here is good example and explanation: https://technet.microsoft.com/en-us/library/bb522522(v=sql.105).aspx – Anton Sep 21 '16 at 04:38

3 Answers3

61

WHEN NOT MATCHED BY TARGET - You should use this clause to insert new rows into the target table. The rows you insert into the table are those rows in the source table for which there are no matching rows in the target.

WHEN NOT MATCHED BY SOURCE - If you want to delete a row from the target table that does not match a row in the source table

Phani
  • 803
  • 8
  • 11
7

Use caution, as you may need to further qualify the WHEN NOT MATCHED BY SOURCE.

For example, if the TARGET table has a column that the SOURCE does not .. and you are setting that target column during the aforementioned insert .. then you'll likely want to define that constraint:

WHEN NOT MATCHED BY SOURCE AND (TARGET.SomeColumn = yada)

Farrukh Normuradov
  • 1,032
  • 1
  • 11
  • 32
PaulieD
  • 71
  • 1
  • 1
  • This is the best thing to mention! I add data daily, and I had to specify that data is being deleted only if WHEN NOT MATCHED BY SOURCE AND (targetTable.some_column is null) THEN DELETE; – pbou May 20 '22 at 12:18
  • CAN we update instead of delete ? i need to update a flag to yes when it is not in source and i don not want to delete . how to do it ? – TheSacredKiller Aug 06 '22 at 13:57
  • @TheSacredKiller WHEN NOT MATCHED BY SOURCE AND (TARGET.SomeColumn = yada) THEN UPDATE SET T.FOO = 'SOMETHING' (T is target) – Tut Nov 21 '22 at 05:45
0

Yow want to match target table with source table.at end your target table should be similar to source table WHEN NOT MATCHED BY SOURCE : focus on SOURCE : there is some rows in target that you don't have any equivalent for it in source table. so you should delete it. WHEN NOT MATCHED BY Target: focus on Target : there is some rows in Source that you don't have any equivalent for it in Target table. so you can insert them

reza roohizadeh
  • 1
  • Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Jun 22 '22 at 13:03