实训小结网站建设,制作企业网站的目的,网站建设的硬件支持,网站建设中心怎么做在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
