在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
begin
MERGE INTO dbo.T_NewBarcodeState_Bak AS targetTable
USING dbo.T_NewBarcodeState AS sourceTable
ON 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 TARGET
THEN 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 SOURCE
THEN DELETE
;
end