SqlServer将A表同步到B表后,根据B表最后同步时间进行实时更新且只保留A表近30天数据—附源码

-- =============================================
-- Author:		<Ashe>
-- Create date: <2023.6.26>
-- Description:	<同步接口日志表数据>
-- =============================================
CREATE PROCEDURE [dbo].[SyncUploadLogRecord]
@I_ReturnMessage NVARCHAR(MAX)='' OUTPUT	-- 返回的信息,支持多语言
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

	DECLARE @LastCreateDate DATETIME = '';
	DECLARE @TmpUploadLog TABLE(
		[id] [NVARCHAR](36) NOT NULL,
		[RequestData] [NVARCHAR](MAX) NULL,
		[jsonData] [NVARCHAR](MAX) NULL,
		[OrBitUserId] [CHAR](12) NULL,
		[UploadResult] [NVARCHAR](MAX) NULL,
		[IsUploadOK] [BIT] NULL,
		[UploadDate] [DATETIME] NULL,
		[ReturnMessage] [NVARCHAR](MAX) NULL,
		[Retdescription] [NVARCHAR](MAX) NULL,
		[ResourceName] [NVARCHAR](50) NULL,
		[ReturnDate] [DATETIME] NULL,
		[CreateDate] [DATETIME] NULL
	)

	--获取最后同步日期  --由于表CreateDate设置索引  故无需再次order by进行排序
	SELECT TOP 1 @LastCreateDate=CreateDate FROM dbo.UploadLogRecord 

	BEGIN 
		--判断接口日志记录表是否第一次同步
		IF ISNULL(@LastCreateDate,NULL)=NULL
		BEGIN
			BEGIN TRY 
				INSERT INTO dbo.UploadLogRecord
				(
					id,
					RequestData,
					jsonData,
					OrBitUserId,
					UploadResult,
					IsUploadOK,
					UploadDate,
					ReturnMessage,
					Retdescription,
					ResourceName,
					ReturnDate,
					CreateDate
				)
				SELECT * FROM dbo.UploadLog 
			END TRY
			BEGIN CATCH
				SET @I_ReturnMessage='同步接口日志记录表失败-1'
				RETURN -1
			END CATCH
		END
		
		INSERT INTO @TmpUploadLog
		(
		    id,
		    RequestData,
		    jsonData,
		    OrBitUserId,
		    UploadResult,
		    IsUploadOK,
		    UploadDate,
		    ReturnMessage,
		    Retdescription,
		    ResourceName,
		    ReturnDate,
		    CreateDate
		)
		SELECT UploadLog.* FROM dbo.UploadLog
		LEFT JOIN dbo.UploadLogRecord ON UploadLogRecord.id = UploadLog.id
		WHERE UploadLog.CreateDate > @LastCreateDate AND ISNULL(UploadLogRecord.id,'')=''

		--检查是否需要同步
		IF (SELECT COUNT(1) FROM @TmpUploadLog)>0
		BEGIN
			BEGIN TRY 
				INSERT INTO dbo.UploadLogRecord
				(
					id,
					RequestData,
					jsonData,
					OrBitUserId,
					UploadResult,
					IsUploadOK,
					UploadDate,
					ReturnMessage,
					Retdescription,
					ResourceName,
					ReturnDate,
					CreateDate
				)
				SELECT * FROM @TmpUploadLog	
			END TRY
			BEGIN CATCH
				SET @I_ReturnMessage='同步接口日志记录表失败-2'
				RETURN -1
			END CATCH
		END

		--删除日志表数据，只保留30天记录
		IF (SELECT COUNT(1) FROM UploadLog WHERE datediff(day,CreateDate,getdate()) > 30)>0
		BEGIN
			BEGIN TRY 
				DELETE  FROM UploadLog WHERE datediff(day,CreateDate,getdate()) > 30
			END TRY
			BEGIN CATCH
				SET @I_ReturnMessage='删除接口日志表失败-3'
				RETURN -1
			END CATCH
		END

	END

	SET @I_ReturnMessage='同步成功！'
	RETURN 0;

END

定时执行存储过程请参照：(1条消息) SqlServer定时执行存储过程_咏絮v的博客-CSDN博客