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

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

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

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

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

一、列出所有的table

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

  1. SELECT A.TABLE_NAME, A.COLUMN_NAME
  2. FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE A
  3. LEFT JOIN SYS.SYSOBJECTS B ON (A.TABLE_NAME = B.NAME AND B.XTYPE = 'U')
  4. WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_NAME), 'ISPRIMARYKEY') = 1
  5. AND SUBSTRING(A.TABLE_NAME,1,6) IN ('income')
  6. 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內的資料


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


  1. DECLARE @SHOWCOLS VARCHAR(256), @TABLENAME VARCHAR(32), @COLNAME VARCHAR(32)
  2. DECLARE @COLSCOMPARE VARCHAR(512), @TABLETMP VARCHAR(32), @MOVEDB VARCHAR(16)
  3. DECLARE @DECLARE_VAR VARCHAR(512), @RECORD_VAR VARCHAR(256), @LOG_VAR VARCHAR(256), @COL_I SMALLINT
  4. /*****************************************************
  5. SETTING VARIABLES DEFAULT VALUES
  6. ******************************************************/
  7. USE Homework;               --設定移轉目的地的DB NAME
  8. DECLARE @MOVEDB_LIST TABLE
  9. (
  10.  DBNAME VARCHAR(16)
  11. )
  12. INSERT INTO @MOVEDB_LIST --設定要移轉的DB NAME
  13. SELECT 'Homework_BAK'  --透過SELECT 'DB_NAME1' UNION SELECT 'DB_NAME2'組成 LIST 
  14. /*****************************************************/
  16. DECLARE DB_CURSOR CURSOR 
  17. FOR
  18.  SELECT DBNAME FROM @MOVEDB_LIST
  19. OPEN DB_CURSOR
  20. FETCH NEXT FROM DB_CURSOR INTO @MOVEDB
  21. --移轉DB CURSOR
  22. WHILE @@FETCH_STATUS = 0
  23. BEGIN
  24.  PRINT '###############=>CURRENT MOVE DB IS '+@MOVEDB
  25.  DECLARE TA_CURSOR CURSOR 
  26.  FOR
  27.      此段語法請見一、列出所有的table
  28.  OPEN TA_CURSOR
  29.  FETCH NEXT FROM TA_CURSOR INTO @TABLENAME, @COLNAME
  31.  SET @TABLETMP = ''
  32.  SET @COLSCOMPARE = ''
  33.  SET @COL_I = 0
  34.  --移轉當下DB'S TABLE CURSOR
  35.  WHILE @@FETCH_STATUS = 0
  36.  BEGIN
  37.   IF @TABLETMP = ''
  38.   BEGIN
  39.       SET @TABLETMP = @TABLENAME
  40.   END 
  41.   ELSE IF @TABLETMP <> @TABLENAME
  42.   BEGIN
  43.       SET @DECLARE_VAR = @DECLARE_VAR+', @NUM INT=0 '
  44.       PRINT '*********移轉'+@TABLETMP+'資料=>PRIMARY KEY COLUMNS '+@SHOWCOLS
  45.       EXEC MERGEDB_PRINT_LOG @DECLARE_VAR, @SHOWCOLS, @TABLETMP, @RECORD_VAR, @LOG_VAR, @COLSCOMPARE, @MOVEDB
  46.       EXEC MERGEDB_TABLE @MOVEDB, @TABLETMP, @COLSCOMPARE
  47.       SET @COLSCOMPARE = ''
  48.       SET @SHOWCOLS = ''
  49.       SET @TABLETMP = @TABLENAME
  50.       SET @DECLARE_VAR = ''
  51.       SET @RECORD_VAR = ''
  52.       SET @COL_I = 0
  53.   END
  54.   IF @COLSCOMPARE = ''
  55.   BEGIN
  56.       SET @COLSCOMPARE = 'TMP.'+@COLNAME+'='+@COLNAME
  57.       SET @SHOWCOLS = @COLNAME
  58.       SET @DECLARE_VAR = 'DECLARE @NAME'+CAST(@COL_I AS VARCHAR(2))+' VARCHAR(32) '
  59.       SET @RECORD_VAR = '@NAME'+CAST(@COL_I AS VARCHAR(2))
  60.       SET @LOG_VAR = '@NAME'+CAST(@COL_I AS VARCHAR(2))
  61.   END 
  62.   ELSE
  63.   BEGIN
  64.      SET @COLSCOMPARE = @COLSCOMPARE + ' AND ' + 'TMP.'+@COLNAME+'='+@COLNAME
  65.      SET @SHOWCOLS = @SHOWCOLS+','+@COLNAME
  66.      SET @DECLARE_VAR = @DECLARE_VAR+', @NAME'+CAST(@COL_I AS VARCHAR(2))+' VARCHAR(32) '
  67.      SET @RECORD_VAR = @RECORD_VAR+',@NAME'+CAST(@COL_I AS VARCHAR(2))
  68.      SET @LOG_VAR = @LOG_VAR+'+'',''+@NAME'+CAST(@COL_I AS VARCHAR(2))
  69.   END
  70.   SET @COL_I = @COL_I + 1
  71.   FETCH NEXT FROM TA_CURSOR INTO @TABLENAME, @COLNAME
  72.  END
  73.  --若無任何符合的table則需避免執行移轉
  74.  IF @TABLETMP <> ''
  75.  BEGIN
  76.      SET @DECLARE_VAR = @DECLARE_VAR+', @NUM INT=0 '
  77.      PRINT '*********移轉'+@TABLETMP+'資料=>PRIMARY KEY COLUMNS '+@SHOWCOLS
  78.      EXEC MERGEDB_PRINT_LOG @DECLARE_VAR, @SHOWCOLS, @TABLETMP, @RECORD_VAR, @LOG_VAR, @COLSCOMPARE, @MOVEDB
  79.      EXEC MERGEDB_TABLE @MOVEDB, @TABLETMP, @COLSCOMPARE
  80.  END 
  81.  CLOSE TA_CURSOR
  82.  DEALLOCATE TA_CURSOR
  83.  
  84.  FETCH NEXT FROM DB_CURSOR INTO @MOVEDB 
  85. END
  86. CLOSE DB_CURSOR
  87. 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來組成並執行之

  1. CREATE PROCEDURE MERGEDB_TABLE
  2. @PARAM1 VARCHAR(16),  --移轉的DB NAME
  3. @PARAM2 VARCHAR(32),  --資料表
  4. @PARAM3 VARCHAR(512)  --欄位比較
  5. WITH RECOMPILE
  6. AS
  7. DECLARE @EXEC_SQL VARCHAR(512)
  8. SET @EXEC_SQL = 'INSERT INTO DBO.'+@PARAM2+' '
  9. SET @EXEC_SQL = @EXEC_SQL+'SELECT * FROM ( '
  10. SET @EXEC_SQL = @EXEC_SQL+'SELECT * FROM '+@PARAM1+'.[DBO].'+@PARAM2+' '
  11. SET @EXEC_SQL = @EXEC_SQL+') TMP '
  12. SET @EXEC_SQL = @EXEC_SQL+'WHERE NOT EXISTS (SELECT * FROM '+@PARAM2+' WHERE '+@PARAM3+') '
  13. print @exec_sql
  14. EXEC (@EXEC_SQL)
  15. GO

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

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

  1. DECLARE @NAME0 VARCHAR(32) , @NUM INT=0 
  2. DECLARE ROW_VALS CURSOR 
  3. FOR 
  4.  SELECT ID FROM Homework_BAK.[DBO].income_idinfos TMP 
  5.  WHERE 
  6.  NOT EXISTS (SELECT * FROM income_idinfos WHERE TMP.ID=ID) 
  7. OPEN ROW_VALS 
  8. FETCH NEXT FROM ROW_VALS INTO @NAME0 
  9. WHILE @@FETCH_STATUS = 0 
  10. BEGIN 
  11.  PRINT @NAME0 SET @NUM=@NUM+1 
  12.  FETCH NEXT FROM ROW_VALS INTO @NAME0 
  13. END 
  14. PRINT '資料筆數'+CAST(@NUM AS VARCHAR(4)) 
  15. CLOSE ROW_VALS 
  16. DEALLOCATE ROW_VALS

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

  1. CREATE PROCEDURE MERGEDB_PRINT_LOG
  2. @PARAM1 VARCHAR(512), --宣告存放欄位的變數
  3. @PARAM2 VARCHAR(256), --顯示的欄位名稱
  4. @PARAM3 VARCHAR(32),  --資料表
  5. @PARAM4 VARCHAR(256), --由CURSOR INSERT VALUE TO欄位變數
  6. @PARAM5 VARCHAR(256), --列印欄位變數的值
  7. @PARAM6 VARCHAR(512), --欄位比較
  8. @PARAM7 VARCHAR(16)   --移轉的DB NAME
  9. WITH RECOMPILE
  10. AS
  11. DECLARE @EXEC_SQL VARCHAR(2048)
  12. SET @EXEC_SQL = @PARAM1
  13. SET @EXEC_SQL = @EXEC_SQL+'DECLARE ROW_VALS CURSOR FOR '
  14. SET @EXEC_SQL = @EXEC_SQL+'SELECT '+@PARAM2+' FROM '+@PARAM7+'.[DBO].'+@PARAM3+' TMP WHERE NOT EXISTS (SELECT * FROM '+@PARAM3+' WHERE '+@PARAM6+') '
  15. SET @EXEC_SQL = @EXEC_SQL+'OPEN ROW_VALS '
  16. SET @EXEC_SQL = @EXEC_SQL+'FETCH NEXT FROM ROW_VALS INTO '+@PARAM4+' '
  17. 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'
  18. PRINT @EXEC_SQL
  19. EXEC (@EXEC_SQL)
  20. 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

留言

張貼留言