SQL 利用merge 同步数据库之间表的数据

同步两个数据库之间两表的数据也许的数据库管理员偶尔需要做的一件事情，下面来记录一下常用的两种方法：

方法一：使用delete、truncate

方法二：使用 merge into  ，Merge是在SQL Server  2008被引入，它能将Insert,Update,Delete简单的并为一句。根据与源表联接的结果，对目标表执行插入、更新或删除操作。例如，根据在另一个表中找到的差异在一个表中插入、更新或删除行，可以对两个表进行同步。通过这个描述，我们 可以看出Merge是关于对于两个表之间的数据进行操作的。 

    可以想象出，需要使用Merge的场景比如:

•     数据同步
•     数据转换
•     基于源表对目标表做Insert,Update,Delete操作 

下面是我把公司正式库里的产品信息表同步到测试库里面的两种方法：

方法一：

1）Truncate  F_test..biitm01t  清空表数据

2）insert  F_Test..biitm01t  select   *    from   F_Formal..biitm01t   把正式库的产品信息插入到test库里面。

 

方法二：首先获取到产品信息表的相关所有列

 SELECT
        表名=case   when   a.colorder=1   then   d.name   else   ''   end,
        字段名=a.name
        FROM   syscolumns   a
        left   join   systypes   b   on   a.xusertype=b.xusertype
        inner   join   sysobjects   d   on   a.id=d.id     and   d.xtype='U'   and     d.name<>'dtproperties'
        left   join   syscomments   e   on   a.cdefault=e.id
        left   join   sys.extended_properties   g   on   a.id=g.major_id   and   a.colid=g.minor_id
        left   join   sys.extended_properties   f   on   d.id=f.major_id   and   f.minor_id=0
        where   d.name='biitm01t'  

         order   by   a.id,a.colorder

你也可以直接  select * from 表   然后一个列一个列记录下(会很累)，通过以上方法直接复制所有字段名，方便以下运用。

得出相关表的所有列名后，下面就是运用到merge into 里面去了。快速运用表字段进行粘贴到merge 里面的方法是：将所有字段复制粘贴到excel里面，然后用EXCEL & 符号合并增加字符号，如图 ，直接复制excel里面值粘贴进去，就不用一个一个输了。

 

merge into F_test..biitm01t as t
using F_Formal..biitm01t as s
on t.itemcd=s.itemcd

when matched     --------------------当上面on 条件相同时，目标表（F_Formal..biitm01t）的数据被更新
then update set
t.itemcd=s.itemcd,
t.itemnm=s.itemnm,
t.itemnm2=s.itemnm2,
t.itemkind=s.itemkind,
t.pogbn=s.pogbn,
t.pjtcd=s.pjtcd

when not matched   ------------------目标表中没有的itemcd，在源表(F_Test)中有，则这些行插入到目标表
then insert values (
s.itemcd,
s.itemnm,
s.itemnm2,
s.itemkind,
s.pogbn,
s.pjtcd

  )
when not matched by source ------目标表存在而源表不存在的数据行，则删除目标表中的这些行
then delete;

运行以上代码成功后，F_Test 库里面的biitm01t 表就跟正式库里面 的biitm01t 表的数据一样了。

备注：上面的Merge关键字后面使用了多个WHEN…THEN语句，而这个语句是可选的.也可以仅仅新增或是仅仅删除，当然第一种方法比较简便，但如果当时的数据被人使用的情况下，使用delete 的方法会给用户带来不便，根据实际情况而定。