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

本文介绍了如何在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

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值