合併資料庫

最近要將有歷史的資料庫合併,因其系統分成北、中、南三區,前端程式大體上相同,資料庫與Web都一式三份,只因使用者位居的地理位置不同,而存取各自的資料庫和程式。但隨著歷史演變,各自系統都稍有不同,而新的規畫要將三個資料庫合成一個。新接手的團隊不清楚舊的做法,因為是跨團隊合作,舊團隊未移交與解釋,這令人頭大(據說該系統已經十幾年到幾十年,以往開發和維護的團隊自己都走馬換將好多代了,不管從程式碼或 DB Schema 都有許多不同的設計與撰寫風格疑惑),將其中的困難點記錄下來,供以後參考:

  • 該工作非做一次而已,有一段長時間的平行,需每隔一陣重做,故儘量建立 T-SQL Script,以周期性執行
  • 選定最大區的資料庫(以下以 db1 代稱),納入其他兩個較小的資料庫(以下以 db2、db3 代稱),僅合併資料內容,延用最大資料庫內的預存程序、檢視、函數等物件。該系統使用 SQL 2000,各有數 GBytes 資料量,物件命名沒有 Owner 的習慣,但我們要移轉到 2008,所有的 DB 都先 backup/restore 到 SQL 2008
  • 先儘量熟悉資料結構,合併細節資料表,一些字典用途的資料表不需合併,合併的資料要避免 PK 重複,FK 要正確,符合 Check…
  • 將 db2/db3 資料載入到 dbTmp,把 identity 欄位加 1 或 2 億後再新增到 db1
  • 透過 SSMS 建立資料表的 CREATE 語法,刪掉 Ideneity,不建立 FK、Default、Check。以該語法到 dbTmp 建立資料表物件
  • 各自資料庫大致相同,但都有些自己的物件,可能是以往團隊臨時測試加上去,但未移除。因此在選擇合併資料表時,透過 sys.tables 取其 Join,共有的才要整合。類似語法如下
    • db2.sys.tables d2 join db1.sys.tables d1
      on d2.name=d1.name
      join db3.sys.tables d3
      on d2.name=d3.name
      where d2.type_desc=’USER_TABLE’ and d2.name NOT LIKE ‘c_%’
      and d2.name not in(…)

  • 透過產生 Insert 語法建立多句欄位對應的語法,避免塞資料時,塞到計算欄位,而後在 dbTmp 執行該批次語法,執行中發現有目標欄位的長度小於來源長度,因此修改先前建 table 的語法檔,重跑

select ‘INSERT [‘ + OBJECT_NAME(d2.object_id)
+ ‘](‘ + ColumnsName +’) SELECT ‘ + ColumnsName +
‘ FROM DB2.dbo.[‘ + OBJECT_NAME(d2.object_id) + ‘] ‘
from
(
SELECT Object_id,LEFT(ColumnsName,LEN(ColumnsName)-1) ColumnsName FROM
(
SELECT Object_id,(
–以XPath 定義查詢出來的資料都是文字
SELECT CAST(name AS sysname) + ‘,’ AS [text()]
FROM sys.columns o
WHERE o.object_id=c.object_id and o.is_computed=0  –不要計算欄位
–不要出現元素名稱,所以PATH(“)
FOR XML PATH(“)) as ColumnsName
FROM sys.tables c
)
TableColumn
) tc
join
(
select d2.*
from db2.sys.tables d2 join db1.sys.tables d1
on d2.name=d1.name
join db3.sys.tables d3
on d2.name=d3.name
where d2.type_desc=’USER_TABLE’ and d2.name NOT LIKE ‘c_%’
and d2.name not in(…)) d2
on tc.object_id=d2.object_id

  • 替有序號意義的欄位建立加 1 億的語法(可能是 identity,亦或不是),而後到 dbTmp 搭配交易執行

select ‘UPDATE [‘ + OBJECT_NAME(object_id)
+ ‘] SET [‘ + name + ‘] = [‘ + name + ‘] +100000000’ from sys.columns
where name like ‘%…’
and OBJECT_NAME(object_id) not like ‘…%’
and  OBJECT_NAME(object_id) not like ‘…%’
and TYPE_NAME(system_type_id)=’int’

  • 設定 DB1 相關資料表的 Identity 可以 Insert,而後 Insert

select ‘SET IDENTITY_INSERT ‘ + OBJECT_NAME(d2.object_id) + ‘ ON
GO
INSERT [‘ + OBJECT_NAME(d2.object_id)
+ ‘](‘ + ColumnsName +’) SELECT ‘ + ColumnsName +
‘ FROM dbTmp.dbo.[‘ + OBJECT_NAME(d2.object_id) + ‘]
GO
SET IDENTITY_INSERT ‘ + OBJECT_NAME(d2.object_id) + ‘ OFF
GO


from
(
SELECT Object_id,LEFT(ColumnsName,LEN(ColumnsName)-1) ColumnsName FROM
(
SELECT Object_id,(
–以XPath 定義查詢出來的資料都是文字
…與上述產生 Insert 語法的定義相同,故省略

但因為 db1 的資料表有 trigger,因此進行到此,需要先請工程師逐步了解 trigger 的定義為何,是用來做彙總、還是更新資料,以決定下一步

發表迴響

在下方填入你的資料或按右方圖示以社群網站登入:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 變更 )

Twitter picture

You are commenting using your Twitter account. Log Out / 變更 )

Facebook照片

You are commenting using your Facebook account. Log Out / 變更 )

Google+ photo

You are commenting using your Google+ account. Log Out / 變更 )

連結到 %s

%d 位部落客按了讚: