Merge query in SQL Server 2008

I having the scenario of loading the data from source table to target table. If the data from source is not present in target, then i need to insert. If it is present in the target table already, then 开发者_如何学运维update the status of the row to 'expire' and insert the column as new row. I used Merge query to do this. I can do insert if not exists and i can do update also. But while trying to insert when matched, it says insert not allowed in 'when matched' clause. Please help me.. Thanks in advance

---

If you want to perform multiple actions for a single row of source data, you need to duplicate that row somehow.

Something like the following (making up table names, etc):

;WITH Source as (
    SELECT Col1,Col2,Col3,t.Dupl
    FROM SourceTable,(select 0 union all select 1) t(Dupl)
)
MERGE INTO Target t
USING Source s ON t.Col1 = s.Col1 and s.Dupl=0 /* Key columns here */
WHEN MATCHED THEN UPDATE SET Expired = 1
WHEN NOT MATCHED AND s.Dupl=1 THEN INSERT (Col1,Col2,Col3) VALUES (s.Col1,s.Col2,s.Col3);

You always want the s.Dupl condition in the not matched branch, because otherwise source rows which don't match any target rows would be inserted twice.

---

From the example you posted as a comment, I'd change:

MERGE target AS tar USING source AS src ON src.id = tar.id
WHEN MATCHED THEN UPDATE SET D_VALID_TO=@nowdate-1, C_IS_ACTIVE='N', D_LAST_UPDATED_DATE=@nowdate
WHEN NOT MATCHED THEN INSERT (col1,col2,col3) VALUES (tar.col1,tar.col2,tar.col3); 

into:

;WITH SourceDupl AS (
    SELECT id,col1,col2,col3,t.Dupl
    FROM source,(select 0 union all select 1) t(Dupl)
)
MERGE target AS tar USING SourceDupl as src on src.id = tar.id AND Dupl=0
WHEN MATCHED THEN UPDATE SET D_VALID_TO=@nowdate-1, C_IS_ACTIVE='N', D_LAST_UPDATED_DATE=@nowdate
WHEN NOT MATCHED AND Dupl=1 THEN INSERT (col1,col2,col3) VALUES (src.col1,src.col2,src.col3);

I've changed the values in the VALUES clause, since in a NOT MATCHED branch, the tar table doesn't have a row to select values from.

---

Check out one of those many links:

• Using SQL Server 2008's MERGE Statement
• MERGE on Technet
• Introduction to MERGE statement
• SQL Server 2008 MERGE

Without actually knowing what your database tables look like, we cannot be of more help - you need to read those articles and figure out yourself how to apply this to your concrete situation.