<<记录通用脚本,以备后用>>
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
GO