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 USING ( SELECT DISTINCT Col1, Col2 FROM Table1 AS cga ) ON dad.AnchorDate = CASE WHEN Col2 BETWEEN DATEADD(dd, - (DAY(cga.col1) - 1), cga.col2) AND CASE WHEN YEAR(cga.col1) = 9999 THEN cga.col1 ELSE DATEADD(d, - 1, DATEADD(m, DATEDIFF(m, 0, cga.col2) + 1, 0)) END THEN dad.Col2 ELSE NULL END WHERE ( dad.AnchorDate = DateKey OR DateKey IS NULL ) ) SOURCE ON TARGET.Col1 = Source.Col1 AND TARGET.Col2 = Source.Col2 WHEN MATCHED THEN UPDATE SET TARGET.Col1 = Source.Col1 ,TARGET.Col2 = Source.Col2 WHEN NOT MATCHED THEN INSERT ( Col1 ,Col2 ) VALUES ( Source.Col1 ,Source.Col2 ); WHEN NOT MATCHED BY SOURCE THEN DELETE
P.V.P.MOhan