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.