SQL Server - 利用T-SQL實作資料庫合併(Merge DataBase)

近期剛好有客戶提到是否可以將多個資料庫做合併，由於這幾個資料庫是同一套系統，只是

座落在不同的單位，因此它們的table schema都是一樣的，只差在資料不一樣，想說是否可以

將多個小單位的DB資料合併到主單位的DB內，如此一來將資料移轉到該主單位DB內。

SQL語法使用環境為：SQL Server 2008 R2

一、列出所有的table

首先，我所想到的是將DB內所有的table都撈出來，同時帶出每個table的primary keys欄位

SELECT A.TABLE_NAME, A.COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE A
LEFT JOIN SYS.SYSOBJECTS B ON (A.TABLE_NAME = B.NAME AND B.XTYPE = 'U')
WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_NAME), 'ISPRIMARYKEY') = 1
AND SUBSTRING(A.TABLE_NAME,1,6) IN ('income')
ORDER BY A.TABLE_NAME, A.ORDINAL_POSITION

在上述的語法中，將當下USE的DB，撈出table名稱開頭為income及對應主鍵欄位做陳列

二、舉例移轉哪些TABLE

在這邊我們將移轉資料庫Homework_BAK的資料到Homework內，並以剛剛提到的兩個table

做示範。

TABLE：income_record

下圖紅色框為主鍵，而藍色框為要移轉到Homework income_record內的資料

TABLE：income_idinfos

下圖紅色框為主鍵，而藍色框為要移轉到Homework income_idinfos內的資料

三、建構執行資料移轉的語法

DECLARE @SHOWCOLS VARCHAR(256), @TABLENAME VARCHAR(32), @COLNAME VARCHAR(32)
DECLARE @COLSCOMPARE VARCHAR(512), @TABLETMP VARCHAR(32), @MOVEDB VARCHAR(16)
DECLARE @DECLARE_VAR VARCHAR(512), @RECORD_VAR VARCHAR(256), @LOG_VAR VARCHAR(256), @COL_I SMALLINT
/*****************************************************
SETTING VARIABLES DEFAULT VALUES
******************************************************/
USE Homework;               --設定移轉目的地的DB NAME
DECLARE @MOVEDB_LIST TABLE
(
 DBNAME VARCHAR(16)
)
INSERT INTO @MOVEDB_LIST --設定要移轉的DB NAME
SELECT 'Homework_BAK'  --透過SELECT 'DB_NAME1' UNION SELECT 'DB_NAME2'組成 LIST 
/*****************************************************/

DECLARE DB_CURSOR CURSOR 
FOR
 SELECT DBNAME FROM @MOVEDB_LIST
OPEN DB_CURSOR
FETCH NEXT FROM DB_CURSOR INTO @MOVEDB
--移轉DB CURSOR
WHILE @@FETCH_STATUS = 0
BEGIN
 PRINT '###############=>CURRENT MOVE DB IS '+@MOVEDB
 DECLARE TA_CURSOR CURSOR 
 FOR
     此段語法請見一、列出所有的table
 OPEN TA_CURSOR
 FETCH NEXT FROM TA_CURSOR INTO @TABLENAME, @COLNAME

 SET @TABLETMP = ''
 SET @COLSCOMPARE = ''
 SET @COL_I = 0
 --移轉當下DB'S TABLE CURSOR
 WHILE @@FETCH_STATUS = 0
 BEGIN
  IF @TABLETMP = ''
  BEGIN
      SET @TABLETMP = @TABLENAME
  END 
  ELSE IF @TABLETMP <> @TABLENAME
  BEGIN
      SET @DECLARE_VAR = @DECLARE_VAR+', @NUM INT=0 '
      PRINT '*********移轉'+@TABLETMP+'資料=>PRIMARY KEY COLUMNS '+@SHOWCOLS
      EXEC MERGEDB_PRINT_LOG @DECLARE_VAR, @SHOWCOLS, @TABLETMP, @RECORD_VAR, @LOG_VAR, @COLSCOMPARE, @MOVEDB
      EXEC MERGEDB_TABLE @MOVEDB, @TABLETMP, @COLSCOMPARE
      SET @COLSCOMPARE = ''
      SET @SHOWCOLS = ''
      SET @TABLETMP = @TABLENAME
      SET @DECLARE_VAR = ''
      SET @RECORD_VAR = ''
      SET @COL_I = 0
  END
  IF @COLSCOMPARE = ''
  BEGIN
      SET @COLSCOMPARE = 'TMP.'+@COLNAME+'='+@COLNAME
      SET @SHOWCOLS = @COLNAME
      SET @DECLARE_VAR = 'DECLARE @NAME'+CAST(@COL_I AS VARCHAR(2))+' VARCHAR(32) '
      SET @RECORD_VAR = '@NAME'+CAST(@COL_I AS VARCHAR(2))
      SET @LOG_VAR = '@NAME'+CAST(@COL_I AS VARCHAR(2))
  END 
  ELSE
  BEGIN
     SET @COLSCOMPARE = @COLSCOMPARE + ' AND ' + 'TMP.'+@COLNAME+'='+@COLNAME
     SET @SHOWCOLS = @SHOWCOLS+','+@COLNAME
     SET @DECLARE_VAR = @DECLARE_VAR+', @NAME'+CAST(@COL_I AS VARCHAR(2))+' VARCHAR(32) '
     SET @RECORD_VAR = @RECORD_VAR+',@NAME'+CAST(@COL_I AS VARCHAR(2))
     SET @LOG_VAR = @LOG_VAR+'+'',''+@NAME'+CAST(@COL_I AS VARCHAR(2))
  END
  SET @COL_I = @COL_I + 1
  FETCH NEXT FROM TA_CURSOR INTO @TABLENAME, @COLNAME
 END
 --若無任何符合的table則需避免執行移轉
 IF @TABLETMP <> ''
 BEGIN
     SET @DECLARE_VAR = @DECLARE_VAR+', @NUM INT=0 '
     PRINT '*********移轉'+@TABLETMP+'資料=>PRIMARY KEY COLUMNS '+@SHOWCOLS
     EXEC MERGEDB_PRINT_LOG @DECLARE_VAR, @SHOWCOLS, @TABLETMP, @RECORD_VAR, @LOG_VAR, @COLSCOMPARE, @MOVEDB
     EXEC MERGEDB_TABLE @MOVEDB, @TABLETMP, @COLSCOMPARE
 END 
 CLOSE TA_CURSOR
 DEALLOCATE TA_CURSOR
 
 FETCH NEXT FROM DB_CURSOR INTO @MOVEDB 
END
CLOSE DB_CURSOR
DEALLOCATE DB_CURSOR

1. 設定基本資料

執行USE語法來挑選移轉目的地的DB，再來建立預移轉的DB LIST，將用在第一層的cursor

看您合併哪些DB

2. 撈出TABLE LIST

宣告TA_CURSOR依據列出所有table的語法，來建構第二層cursor走訪table，建構要傳進

store procedure的參數值!

3. 建構移轉資料及印出LOG的語法

接下來的T-SQL目的很簡單，主要是為了組出移轉資料的語法

以本範例的income_idinfos來說，產生的移轉語法將如下：

INSERT INTO DBO.income_idinfos 
    SELECT * FROM ( SELECT * FROM Homework_BAK.[DBO].income_idinfos ) TMP 
WHERE NOT EXISTS (SELECT * FROM income_idinfos WHERE TMP.ID=ID)

組成這個語法主要是透過store procedure MERGEDB_TABLE來組成並執行之

CREATE PROCEDURE MERGEDB_TABLE
@PARAM1 VARCHAR(16),  --移轉的DB NAME
@PARAM2 VARCHAR(32),  --資料表
@PARAM3 VARCHAR(512)  --欄位比較
WITH RECOMPILE
AS
DECLARE @EXEC_SQL VARCHAR(512)
SET @EXEC_SQL = 'INSERT INTO DBO.'+@PARAM2+' '
SET @EXEC_SQL = @EXEC_SQL+'SELECT * FROM ( '
SET @EXEC_SQL = @EXEC_SQL+'SELECT * FROM '+@PARAM1+'.[DBO].'+@PARAM2+' '
SET @EXEC_SQL = @EXEC_SQL+') TMP '
SET @EXEC_SQL = @EXEC_SQL+'WHERE NOT EXISTS (SELECT * FROM '+@PARAM2+' WHERE '+@PARAM3+') '
print @exec_sql
EXEC (@EXEC_SQL)
GO

接著為印出移轉哪些資料的LOG語法組成

以本範例的income_idinfos來說，產生的印出LOG語法將如下：

DECLARE @NAME0 VARCHAR(32) , @NUM INT=0 
DECLARE ROW_VALS CURSOR 
FOR 
 SELECT ID FROM Homework_BAK.[DBO].income_idinfos TMP 
 WHERE 
 NOT EXISTS (SELECT * FROM income_idinfos WHERE TMP.ID=ID) 
OPEN ROW_VALS 
FETCH NEXT FROM ROW_VALS INTO @NAME0 
WHILE @@FETCH_STATUS = 0 
BEGIN 
 PRINT @NAME0 SET @NUM=@NUM+1 
 FETCH NEXT FROM ROW_VALS INTO @NAME0 
END 
PRINT '資料筆數'+CAST(@NUM AS VARCHAR(4)) 
CLOSE ROW_VALS 
DEALLOCATE ROW_VALS

組成這個語法主要是透過store procedure MERGEDB_PRINT_LOG來組成並執行之

CREATE PROCEDURE MERGEDB_PRINT_LOG
@PARAM1 VARCHAR(512), --宣告存放欄位的變數
@PARAM2 VARCHAR(256), --顯示的欄位名稱
@PARAM3 VARCHAR(32),  --資料表
@PARAM4 VARCHAR(256), --由CURSOR INSERT VALUE TO欄位變數
@PARAM5 VARCHAR(256), --列印欄位變數的值
@PARAM6 VARCHAR(512), --欄位比較
@PARAM7 VARCHAR(16)   --移轉的DB NAME
WITH RECOMPILE
AS
DECLARE @EXEC_SQL VARCHAR(2048)
SET @EXEC_SQL = @PARAM1
SET @EXEC_SQL = @EXEC_SQL+'DECLARE ROW_VALS CURSOR FOR '
SET @EXEC_SQL = @EXEC_SQL+'SELECT '+@PARAM2+' FROM '+@PARAM7+'.[DBO].'+@PARAM3+' TMP WHERE NOT EXISTS (SELECT * FROM '+@PARAM3+' WHERE '+@PARAM6+') '
SET @EXEC_SQL = @EXEC_SQL+'OPEN ROW_VALS '
SET @EXEC_SQL = @EXEC_SQL+'FETCH NEXT FROM ROW_VALS INTO '+@PARAM4+' '
SET @EXEC_SQL = @EXEC_SQL+'WHILE @@FETCH_STATUS = 0 BEGIN PRINT '+@PARAM5+' SET @NUM=@NUM+1 FETCH NEXT FROM ROW_VALS INTO '+@PARAM4+' END PRINT ''資料筆數''+CAST(@NUM AS VARCHAR(4)) CLOSE ROW_VALS DEALLOCATE ROW_VALS'
PRINT @EXEC_SQL
EXEC (@EXEC_SQL)
GO

移轉後印出的結果如下：

在這邊印出的data為該table row的主鍵欄位的value

查詢Homework下的income_record table發現多了三列的資料(Homework_BAK)

查詢Homework下的income_idinfos table發現多了兩列的資料(Homework_BAK)

最後，其實這個T-SQL語法要做的事情其實單純到不行，目的就是為了組出要傳到

MERGEDB_TABLE、MERGEDB_PRINT_LOG的參數值罷了，進而執行該段字串SQL

語法，進而達到目的。

PS. 也許有更簡單可以達到合併資料庫下資料表資料的方法，小弟我目前也只想到這個

土法煉鋼的方法orz

Zhi-Bin's 談天說地

搜尋此網誌

SQL Server - 利用T-SQL實作資料庫合併(Merge DataBase)

留言

張貼留言