柯桥区网站建设网站建设与网站设计

在SQL server （2008以上版本）中当需要将一个表（可能另一个库）中数据同步到另一个表中时，可以考虑使用merge语句。
只需要提供：
1.目标表 （target table）
2.数据源表 (source table)
3.连接条件
4.当行匹配时执行更新语句
5.当行不匹配目标表时执行更新语句
6.当行不匹配源表时执行删除语句

------------------------------------------------------------------
--备份履历表【T_NewBarcodeState】到备份表【T_NewBarcodeState_Bak】
------------------------------------------------------------------
--CREATE TABLE [dbo].[T_NewBarcodeState_Bak](
--	[ID] [varchar](50),
--	[Barcode] [varchar](18),
--	[ProductCode] [varchar](10),
--	[StepNum] [int],
--	[ProductClassify] [nvarchar](20),
--	[ProductType] [nvarchar](20),
--	[CurrentPro] [nvarchar](20),
--	[CurrentProStep] [nvarchar](20),
--	[CurrentProStepState] [nvarchar](20),
--	[QualityState] [nvarchar](10),
--	[Location] [nvarchar](2000),
--	[Defect] [nvarchar](2000),
--	[Disposal] [nvarchar](2000),
--	[Remarks] [nvarchar](2000),
--	[UserName] [nvarchar](20),
--	[ComputerName] [nvarchar](200),
--	[AddTime] [datetime],
--	[SortId] [bigint],
--	[UntreateDefect] [varchar](255),
--	[Remark2] [varchar](255),
--)
alter proc Sp_MERGE_NewBarcodeState
as
beginMERGE INTO dbo.T_NewBarcodeState_Bak AS targetTableUSING dbo.T_NewBarcodeState AS sourceTableON targetTable.ID = sourceTable.ID--当行匹配时执行更新语句WHEN MATCHED THEN UPDATE SET targetTable.Barcode = sourceTable.Barcode,targetTable.ProductCode = sourceTable.ProductCode,targetTable.StepNum = sourceTable.StepNum,targetTable.ProductClassify = sourceTable.ProductClassify,targetTable.ProductType = sourceTable.ProductType,targetTable.CurrentPro = sourceTable.CurrentPro,targetTable.CurrentProStep = sourceTable.CurrentProStep,targetTable.CurrentProStepState = sourceTable.CurrentProStepState,targetTable.QualityState = sourceTable.QualityState,targetTable.Location = sourceTable.Location,targetTable.Defect = sourceTable.Defect,targetTable.Disposal = sourceTable.Disposal,targetTable.Remarks = sourceTable.Remarks,targetTable.UserName = sourceTable.UserName,targetTable.ComputerName = sourceTable.ComputerName,targetTable.AddTime = sourceTable.AddTime,targetTable.SortId = sourceTable.SortId,targetTable.UntreateDefect = sourceTable.UntreateDefect,targetTable.[Remark2] = sourceTable.[Remark2]--当行不匹配目标表时执行更新语句WHEN NOT MATCHED BY TARGETTHEN INSERT VALUES (ID,Barcode,ProductCode,StepNum,ProductClassify,ProductType,CurrentPro,CurrentProStep,CurrentProStepState,QualityState,Location,Defect,Disposal,Remarks,UserName,ComputerName,AddTime,SortId,UntreateDefect,Remark2)--当行不匹配源表时执行删除语句WHEN NOT MATCHED BY SOURCETHEN DELETE;
end