SQL Server 2008 合并两个表 [英] Merge two Table in SQL Server 2008

问题描述

我有 2 个表，其中一个是 Stage 表，其架构与 main 相同，我想将数据从 stage 表更新到主表，并将 ID 列作为参照键.我曾尝试在 SQL 中使用 Merge，但面临着问题，因为只有很少的值需要更新，并且需要将数千个新值插入到主表中.例如:

I have 2 table one is a Stage table whose schema is exac to main,i want to update the data from stage table to main table with the ID column as refrential key. I have tried using Merge in SQL but facing problems with that as there are only few values to be updated and thousand of new values need to be inserted to the main table. e.g:

MERGE TABLE tblMain AS main
USING (SELECT ID,NAME,EMAIL_ID FROM tblStage) as stage
ON main.ID=stage.ID
WHEN MATCHED THEN UPDATE SET
main.ID=stage.ID,
main.NAME=stage.NAME,
main.EMAIL_ID=stage.EMAIL_ID
WHEN NOT MATCHED THEN INSERT VALUES 
(
----I am stucked here what to write as there are thousands of values:(
)

推荐答案

可以参考insert部分的merge源码，如:

You can refer to the merge source in the insert part, like:

when not matched then insert
  (id, name, email_id) 
  values (stage.id, stage.name, stage.email_id)

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