[Oracle]從A資料表同步log資料到B資料表

由於LOG資料表比較不會有update資料的問題，
所以可以忽略不更新舊資料

目標如下：

1. 清理舊資料：刪除 2 年前的資料。只保留兩年內的資料

2. 清理異常新資料：刪除前 2 天後的資料。(避免異常資料)

3. 增量同步：抓取 B 表最大時間，將 A 表更新的資料補進 B 表。

程式Sync_b_from_a，包含三個變數
A資料表
B資料表
C基準欄位 datetime

刪除B資料表的C基準欄位，2年以前的資料。
刪除B資料表的C基準欄位，前2天以後的資料。  (這可以依需求調整(ㄈ
先查詢B資料表的C基準欄位，找到B資料表中C基準欄位的最大值，
從A資料表insert進B資料表，條件是「A資料表的C基準欄位」必須大於「B資料表中C基準欄位的最大值」

執行

SET SERVEROUTPUT ON; BEGIN     sync_b_from_a(         p_table_a  => 'REMOTE.TABLE_A@DBLINK',         p_table_b  => ' LOCAL.TABLE_B',         p_col_name => ' CREATE_DATE' -- 您的基準欄位     ); END;

 

程式

CREATE OR REPLACE PROCEDURE sync_b_from_a (     p_table_a IN VARCHAR2, -- 來源資料表 (例如 'REMOTE.TABLE_A@DBLINK`')     p_table_b IN VARCHAR2, -- 目標資料表 (例如 'LOCAL.TABLE_B')     p_col_name IN VARCHAR2  -- 基準時間欄位名稱 (例如 'CREATE_DATE') ) AS     v_sql         VARCHAR2(4000);     v_max_time_b  DATE;     v_row_count   NUMBER; BEGIN     -- 1. 刪除 B 表中 2 年以前的資料     v_sql := 'DELETE FROM ' || p_table_b ||              ' WHERE ' || p_col_name || ' < ADD_MONTHS(SYSDATE, -24)';     EXECUTE IMMEDIATE v_sql;     v_row_count := SQL%ROWCOUNT;     DBMS_OUTPUT.PUT_LINE('已刪除 2 年前舊資料: ' || v_row_count || ' 筆');       -- 2. 刪除 B 表中前 2 天以後 (未來) 的資料     v_sql := 'DELETE FROM ' || p_table_b ||              ' WHERE ' || p_col_name || ' > SYSDATE - 2';     EXECUTE IMMEDIATE v_sql;     v_row_count := SQL%ROWCOUNT;     DBMS_OUTPUT.PUT_LINE('已刪除前 2 天後資料: ' || v_row_count || ' 筆');       -- 3. 找出 B 表中該時間欄位的最大值     v_sql := 'SELECT MAX(' || p_col_name || ') FROM ' || p_table_b;     EXECUTE IMMEDIATE v_sql INTO v_max_time_b;       -- 如果 B 表是空的，給予一個預設起始值 (例如 2 年前)     IF v_max_time_b IS NULL THEN         v_max_time_b := ADD_MONTHS(SYSDATE, -24);     END IF;         DBMS_OUTPUT.PUT_LINE('B 表目前最大時間點: ' || TO_CHAR(v_max_time_b, 'YYYY-MM-DD HH24:MI:SS'));       -- 4. 從 A 表 Insert 大於該時間點的資料進 B 表     v_sql := 'INSERT INTO ' || p_table_b ||              ' SELECT * FROM ' || p_table_a ||              ' WHERE ' || p_col_name || ' > :max_t';         EXECUTE IMMEDIATE v_sql USING v_max_time_b;     v_row_count := SQL%ROWCOUNT;     DBMS_OUTPUT.PUT_LINE('從 A 表新增資料至 B 表: ' || v_row_count || ' 筆');       -- 5. 完成     COMMIT;   EXCEPTION     WHEN OTHERS THEN         ROLLBACK;         DBMS_OUTPUT.PUT_LINE('錯誤發生: ' || SQLERRM);         RAISE; END; /

[SQL Server]利用Linked Server跨資料庫單向同步資料表

以下預存程序達成這些目標

1. 查詢LocalDB的資料表，若RemoteDB不存在同名資料表則建立資料表。

2. 清空列出的RemoteDB端的資料表，並寫入資料。

3. 印出LocalDB端的資料表筆數、RemoteDB端的資料表筆數

注意事項

1. LinkedServer使用的帳號權限要夠。

2. 建立Cursor的時候可以指定同步哪些資料表，若不指定則會查詢LocalDB的所有資料表。

3. 若是資料表的欄位有異動，可移除RemoteDB端的資料表，執行預存程序會直接依照LocalDB建立資料表即可同步資料表。

執行語法

SyncTablesToRemote     @LocalDB='LocalDBName',     @RemoteDB='RemoteDBName',     @LinkedServer='LinkedServerName'

程式內容

CREATE PROCEDURE [dbo].[SyncTablesToRemote]     @LocalDB NVARCHAR(128),     @RemoteDB NVARCHAR(128),     @LinkedServer NVARCHAR(128) AS BEGIN     SET NOCOUNT ON;       DECLARE @TableName NVARCHAR(128);     DECLARE @SQL NVARCHAR(MAX);     DECLARE @LocalCountOut INT;     DECLARE @RemoteCountOut INT;     DECLARE @ColumnList NVARCHAR(MAX);       DECLARE @Tables TABLE (TableName NVARCHAR(128));       INSERT INTO @Tables     SELECT name FROM sys.tables WHERE is_ms_shipped = 0 -- 可以指定要同步那些資料表 and name in ('TableName1','TableName2','TableName3')      ;     DECLARE TableCursor CURSOR FOR     SELECT TableName FROM @Tables;       OPEN TableCursor;     FETCH NEXT FROM TableCursor INTO @TableName;       WHILE @@FETCH_STATUS = 0     BEGIN         -- 建立遠端資料表（若不存在）         SET @SQL = '         IF NOT EXISTS (             SELECT 1 FROM OPENQUERY([' + @LinkedServer + '],                 ''SELECT TABLE_NAME FROM [' + @RemoteDB + '].INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''''' + @TableName + ''''''')         )         BEGIN             DECLARE @CreateSQL NVARCHAR(MAX);             SELECT @CreateSQL = ''CREATE TABLE [' + @TableName + '] ('' + STUFF((                 SELECT '', ['' + c.name + ''] '' +                     CASE                         WHEN t.name IN (''varchar'', ''nvarchar'') THEN                             CASE                                 WHEN c.max_length = -1 OR c.max_length > 4000 THEN t.name + ''(4000)''                                 ELSE t.name + ''('' + CAST(c.max_length AS VARCHAR) + '')''                             END                         WHEN t.name IN (''decimal'', ''numeric'') THEN t.name + ''('' + CAST(c.precision AS VARCHAR) + '','' + CAST(c.scale AS VARCHAR) + '')''                         ELSE t.name                     END +                     CASE WHEN c.is_nullable = 0 THEN '' NOT NULL'' ELSE '' NULL'' END                 FROM [' + @LocalDB + '].sys.columns c                 JOIN [' + @LocalDB + '].sys.types t ON c.user_type_id = t.user_type_id                 WHERE c.object_id = OBJECT_ID(''' + @LocalDB + '.dbo.' + @TableName + ''')                 FOR XML PATH(''''), TYPE).value(''.'', ''NVARCHAR(MAX)'')             , 1, 2, '''') + '')''               EXEC(''USE [' + @RemoteDB + ']; '' + @CreateSQL) AT [' + @LinkedServer + '];               PRINT ''已於遠端 [' + @LinkedServer + '] 建立資料表 [' + @TableName + ']''         END         '         EXEC(@SQL);           -- 清空遠端資料表         SET @SQL = '         EXEC(''             USE [' + @RemoteDB + '];             TRUNCATE TABLE [' + @TableName + '];         '') AT [' + @LinkedServer + ']';         EXEC(@SQL);           -- 產生欄位清單         SELECT @ColumnList = STUFF((             SELECT ', ' + QUOTENAME(name)             FROM sys.columns             WHERE object_id = OBJECT_ID(@LocalDB + '.dbo.' + @TableName)             FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')         , 1, 2, '');           -- 匯入資料到遠端         SET @SQL = '         INSERT INTO OPENQUERY([' + @LinkedServer + '],             ''SELECT ' + @ColumnList + ' FROM [' + @RemoteDB + '].dbo.[' + @TableName + ']'')         SELECT ' + @ColumnList + ' FROM [' + @LocalDB + '].dbo.[' + @TableName + '];';         EXEC(@SQL);           -- 比對筆數（簡化版）         DECLARE @TmpLocalCount TABLE (C INT);         SET @SQL = 'SELECT COUNT(1) AS C FROM [' + @LocalDB + '].dbo.[' + @TableName + ']';         INSERT INTO @TmpLocalCount EXEC(@SQL);         SELECT @LocalCountOut = C FROM @TmpLocalCount;           DECLARE @TmpRemoteCount TABLE (C INT);         SET @SQL = '         SELECT * FROM OPENQUERY([' + @LinkedServer + '],             ''SELECT COUNT(1) AS C FROM [' + @RemoteDB + '].dbo.[' + @TableName + ']'')';         INSERT INTO @TmpRemoteCount EXEC(@SQL);         SELECT @RemoteCountOut = C FROM @TmpRemoteCount;           PRINT '資料表 [' + @TableName + '] 已同步完成，本地筆數為 ' + CAST(@LocalCountOut AS NVARCHAR) + '，遠端筆數為 ' + CAST(@RemoteCountOut AS NVARCHAR);           FETCH NEXT FROM TableCursor INTO @TableName;     END       CLOSE TableCursor;     DEALLOCATE TableCursor; END