[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