Using Merge statement in SQL server

Question

Using Merge in sql server i need load only 1 year data from the source table. Please help me to built the logic.

 DATEADD(year,-1,getdate()) -- one year date
 
MERGE target_table USING source_table
ON merge_condition
WHEN MATCHED
    THEN update_statement
WHEN NOT MATCHED
    THEN insert_statement;

Use a subquery for `source_table` with a `WHERE`, like shown in the example in the [documentation](https://learn.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql?view=sql-server-ver15#d-inserting-the-results-of-the-merge-statement-into-another-table). — Thom A, Jun 23 '20 at 12:46

score 3 · Answer 1 · answered Jun 23 '20 at 12:59

As Larnu recommends:

MERGE target_table 
USING (SELECT * FROM source_table WHERE somedatecolumn > DATEADD(year,-1,getutcdate())) x
ON merge_condition
WHEN MATCHED
    THEN update_statement
WHEN NOT MATCHED
    THEN insert_statement

The source table that drives a merge is the only data that will be invovled in the merge. If source_table has 1000 rows, but only 10 from the past year, then the MERGE will operate over 10 rows. If the destination table has 10000 rows, and 7 of these rows match those in the 10 rows from the source, 7 updates will be made and 3 inserts will be made. The destination table will have 10003 rows at the end of the operation

Using Merge statement in SQL server

1 Answers1