How do I write a merge query deletion of records

See more:

SQL

SSIS

mergesort

Hi Friends,

I have an issue while loading data through SSIS through CSV files and writing Merge Query.

I have a table Name [REVENUE_ADJUST_TRX] and it has a
composite key which is [HEADER_TRX_LINE_ID] & [TRX_NUM].

The Problem statement is i have to load Incremental data but while loading i have to Delete the Existing records.

There can be multiple [HEADER_TRX_LINE_ID] for the same [TRX_NUM], If a new journal entry comes in for an existing [TRX_NUM] then all the entries for that [TRX_NUM] should be removed and rewritten. Example below:

[HEADER_TRX_LINE_ID]       [TRX_NUM]     Journal
23666303		   12206047       155333
30638103		   12206047       155333
19866662		   12206047       155333
19361157		   12206048       161045

I have a staging table and Final Table implemented in SQL Server.

What I have tried:

Write now i have implemented Merge statement with Insert and Update by truncating the Staging table always when i do a incremental load.