SQL Server - 使用 Merge 语句实现表数据之间的对比同步

原创已于 2023-11-20 17:16:03 修改

· 714 阅读

1 ·

版权

文章标签：

#sql

于 2023-11-20 17:14:41 首次发布

本文介绍了如何在SQLServer2008及以上版本中使用merge语句，将一个库中的T_NewBarcodeState表数据同步到另一个表T_NewBarcodeState_Bak中，包括匹配、插入和删除操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

在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