在SQL Server 2008中合并查询 [英] 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);

您总是希望在不匹配的分支中使用s.Dupl条件，因为否则，与任何目标行都不匹配的源行将被插入两次.

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); 

进入:

;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);

我已经更改了VALUES子句中的值，因为在NOT MATCHED分支中，tar表没有一行可以从中选择值.

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.

这篇关于在SQL Server 2008中合并查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！