数据库同步-重置同步数据

来源：互联网发布：明天教室网络课怎么样编辑：程序博客网时间：2024/05/01 16:22

<<记录通用脚本，以备后用>>

USE [DB_Name]--设置数据库
GO

CREATE PROCEDURE [dbo].[Proc_ResetSyncTable]
--参数为“表明”和“主键列名”
@SyncTableName AS varchar(50),@SyncTableId AS varchar(50)
AS
BEGIN
BEGIN TRY
  IF ''<>ltrim(rtrim(@SyncTableName)) AND ''<>ltrim(rtrim(@SyncTableId))
  BEGIN
   --变量声明
   DECLARE @ExecSql varchar(200) = ''--表影响行数
   DECLARE @SyncRowCount int = 0--表影响行数
   DECLARE @SyncCursorStatus int = -3--游标状态为*不存在
   DECLARE @OccurId uniqueidentifier = null
   DECLARE @OccurArea nvarchar(50) = ''

   --SELECT CURSOR_STATUS('global','cursor_sync_data') AS RESULT
   --)1:游标的结果集至少有一行
   --)0:游标的结果集为空
   --)-1:游标被关闭
   --)-2:游标不适用
   --)-3:游标不存在

   PRINT '---------------------------------------'
   SET @OccurArea =@SyncTableName
   PRINT '开始同步表：' + @OccurArea
   SELECT @SyncCursorStatus=CURSOR_STATUS('global','cursor_sync_data')
   IF -3=@SyncCursorStatus
   BEGIN
    --拼接SQL语句
    SET @ExecSql = 'DECLARE cursor_sync_data CURSOR FOR SELECT ' + @SyncTableId + ' FROM ' + @SyncTableName + ' FOR READ ONLY'
    IF EXISTS(SELECT * FROM syscolumns where id=object_id(@SyncTableName) and name='CreatedOn')--按创建时间排序
     SET @ExecSql = 'DECLARE cursor_sync_data CURSOR FOR SELECT ' + @SyncTableId + ' FROM ' + @SyncTableName + ' ORDER BY CreatedOn ASC FOR READ ONLY'

    EXEC(@ExecSql)--执行SQL语句建立游标
    OPEN cursor_sync_data--打开游标
    FETCH NEXT FROM cursor_sync_data INTO @OccurId--提取游标第一行
    --循环提取游标内容
    SET @SyncRowCount=0
    WHILE @@FETCH_STATUS=0
    BEGIN
     SET @SyncRowCount=@SyncRowCount+1
     IF NOT EXISTS(SELECT * FROM [dbo].[WMG_Sync] WHERE [OccurId]=@OccurId)
     BEGIN
      insert into WMG_Sync(SyncId,OccurId,OccurArea,OccurType,CreatedOn,SyncOn,IsSynced)
      values(newId(),@OccurId,@OccurArea,1,getdate(),null,0);
     END
     FETCH NEXT FROM cursor_sync_data INTO @OccurId
    END
    CLOSE cursor_sync_data--关闭游标
    DEALLOCATE cursor_sync_data--释放游标资源
    PRINT '完成同步表：' + @OccurArea + ' -->>行数：' + CAST(@SyncRowCount AS varchar(50))
   END
  END
END TRY
BEGIN CATCH END CATCH
END

0 0