SQL Merge deleting all my rows in target table

Question!

I'm using a powershell function to perform a SQL MERGE. My inserts are working, but my delete is deleting all the rows in my target table. My SQL statement looks like this with the variables being substituted by PowerShell:

MERGE INTO $($Target) AS Target
USING $($Target)_TEMP AS Source
ON Target.[$($PrimaryKey)] = Source.[$($PrimaryKey)]
WHEN NOT MATCHED THEN
INSERT ($InsertColumns) VALUES ($InsertValues)
WHEN NOT MATCHED BY Source THEN
    DELETE;

You can see the full powershell function here: http://pastebin.com/uWY6VfmU

Example Table Data:

domsamgrp  (primary key)        samaccountname  GroupName   Domain  whenchanged
MYDOMAIN\user1\Tableau_Client   user1   Tableau_Client  MYDOMAIN    55:46.0
MYDOMAIN\user2\Tableau_Client   user2   Tableau_Client  MYDOMAIN    55:46.0
MYDOMAIN\user3\Tableau_CMO      user3   Tableau_CMO     MYDOMAIN    55:48.0
MYDOMAIN\user1\Tableau_EDM      user1   Tableau_EDM     MYDOMAIN    55:50.0
MYDOMAIN\user5\Tableau_EDM      user5   Tableau_EDM     MYDOMAIN    55:49.0


Answers

Sorry Everyone. I found the problem in the elsewhere in my code. I was producing source tables with with only a few records. My MERGE was working properly. It was the my code to build the source tables that was horribly wrong. Closing this question.



I think your condition for DELETE is wrong

WHEN **NOT** MATCHED **BY Source** THEN
DELETE;

Remove NOT



This video can help you solving your question :)
By: admin