十堰百度网站建设,做网站郴州,网站文件目录结构,深圳微信小程序制作公司在SQL server #xff08;2008以上版本#xff09;中当需要将一个表#xff08;可能另一个库#xff09;中数据同步到另一个表中时#xff0c;可以考虑使用merge语句。 只需要提供#xff1a; 1.目标表 #xff08;target table#xff09; 2.数据源表 (source table) …在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
