i have written a merge Statement where i am facing trouble to delete the data basing on Where Clause Condition.
Let me explain my scenario Clearly
1) Case 1 : For example i have inserted Data from Source to Target based on Date Key Condition.Take an Instance 10 Records Inserted.
2) Case 2 : For example some changes in the records and it has been updated through the Merge Statement .
3) Case 3 : For the Same Date key based on Conditions now three records has came and need to be inserted and rest should be deleted for that Date Key.
How i need to proceed on this before 10 records are not getting deleted and new records adding for that one
My Example Code :
MERGE INTO TargetTable AS Target
SELECT DISTINCT Col1,
FROM Table1 AS cga
ON dad.AnchorDate = CASE
WHEN Col2 BETWEEN DATEADD(dd, - (DAY(cga.col1) - 1), cga.col2)
WHEN YEAR(cga.col1) = 9999
ELSE DATEADD(d, - 1, DATEADD(m, DATEDIFF(m, 0, cga.col2) + 1, 0))
dad.AnchorDate = DateKey
OR DateKey IS NULL
ON TARGET.Col1 = Source.Col1
AND TARGET.Col2 = Source.Col2
SET TARGET.Col1 = Source.Col1
,TARGET.Col2 = Source.Col2
WHEN NOT MATCHED
WHEN NOT MATCHED BY SOURCE