Merge Into 用法
从一个数据库的一张表同步数据到另外一个数据库的一张表,同步的数据不是insert就是update。
一般做法是先判断当前数据在另外一张表存不存在,存在则更新,不存在则插入。需要一次查询判断:exist或count(*)>0,
然而用Merge Into 更为简洁:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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;