Merge Into 用法

从一个数据库的一张表同步数据到另外一个数据库的一张表，同步的数据不是insert就是update。

一般做法是先判断当前数据在另外一张表存不存在，存在则更新，不存在则插入。需要一次查询判断:exist或count(*)>0,

然而用Merge Into 更为简洁：

CREATE PROC [dbo].[XXX_SynchroMaterial]

@userID nvarchar(50)

as

begin

declare @nResult int; --同步多少条物料

declare @totalRows    int;

set @nResult=0;

--建立临时数据表

create table #tmpMateriel

(

[id] int IDENTITY(1,1) ,

MaterielGUID nvarchar(50),

MaterielCode nvarchar(50),

MaterielName  nvarchar(255),

ParentNode nvarchar(50),

IfLeafNode bit,

Material_Category int null,

Raw_Material_Category int null

)  

--读取物料中间表数据，插入到临时表

insert into #tmpMateriel(MaterielGUID,MaterielCode,MaterielName,ParentNode,IfLeafNode,Material_Category,Raw_Material_Category)

SELECT [MaterielGUID]

      ,[MaterielCode]

      ,[MaterielName]

      ,[ParentNode]

      ,[IfLeafNode]

      ,CASE WHEN (MaterielCode like '1.03%' or MaterielCode like '2.02%')  THEN 1

           WHEN (MaterielCode like '1.04%' or MaterielCode like '2.03%') THEN 2

           ELSE 3

       END

      ,CASE WHEN [IfLeafNode]=1 THEN 4  --小料

       ELSE null

       END

  FROM [ChwareXXXInterface].[dbo].[Tab_Materiel_Interface] where [If_Read]=0;

select @nResult=COUNT(*) from #tmpMateriel;  

Merge Into [ChwareXXX].[dbo].[Tab_Material] t

USING (select [MaterielCode],[MaterielName],[ParentNode],[IfLeafNode],[Material_Category],[Raw_Material_Category] from #tmpMateriel)as s

on t.[MaterialCode]=s.[MaterielCode]

When Matched Then Update set t.[MaterialName]=s.[MaterielName],

                             t.[ParentNode]=s.[ParentNode],

                             t.[IfLeafNode]=s.[IfLeafNode],

                             t.[Material_Category]=s.[Material_Category],

                             t.[Raw_Material_Category]=s.[Raw_Material_Category],

                             t.[ModifyUser]=@userID,

                             t.[ModifyDT]=getdate()

When Not Matched Then Insert ([MaterialCode],[MaterialName],[ParentNode],[IfLeafNode],[Material_Category],[Raw_Material_Category],[CreateUser],[CreateDt])

                      values (s.[MaterielCode],s.[MaterielName],s.[ParentNode],s.[IfLeafNode],s.[Material_Category],s.[Raw_Material_Category],@userID,getdate());

--更新接口表读取状态

update [ChwareXXXInterface].[dbo].[Tab_Materiel_Interface] set [If_Read]=1,ReadDT=GETDATE()where MaterielCode in (select MaterielCode from #tmpMateriel);

--删除临时表

drop table #tmpMateriel;

select @nResult; --同步成功多少条数据

end

GO

总结：

通过MERGE语句，根据一张表或子查询的连接条件对另外一张表进行查询，
连接条件匹配上的进行UPDATE，无法匹配的执行INSERT。
这个语法仅需要一次全表扫描就完成了全部工作，执行效率要高于INSERT＋UPDATE。

语法：
MERGE [INTO [schema .] table [t_alias]
USING [schema .] { table | view | subquery } [t_alias]
ON ( condition )
WHEN MATCHED THEN merge_update_clause
WHEN NOT MATCHED THEN merge_insert_clause;

巴特西

Merge Into 用法

最新文章

热门文章