[SQL][Excel]利用 Excel 來比對兩個資料庫的數據量是否相同

[SQL][Excel]利用 Excel 來比對兩個資料庫的數據量是否相同

臨時有個朋友給個好玩的題目,他們在兩個不能相互連接的 Site 上面各有個資料庫,透過一些方式將甲地的資料同步到乙地去,想要有個「比較簡單的方式來驗證兩邊的資料筆數都有一致」,而且資料表如果有少也要能找出來。懶得去寫程式了,就要他自己去用 Excel 處理一下,因此順手把相關步驟整理一下,方便以後有需要可以提供給有需要的朋友使用。

 

首先可以透過個簡單的指令來取出資料庫資料表的名稱和資料筆數,這個在之前有介紹過 ( [SQL]取得資料庫內 Table 的大小 )


declare @a table(name sysname, rows integer, recevered varchar(20), data varchar(20),
  index_size varchar(20),unused varchar(20));

insert into @a exec sys.sp_MSforeachtable "sys.sp_spaceused '?'";

select name,rows from @a order by name ;

在兩邊的機器上執行完後,在輸出結果上選擇「隨標頭一同複製」把結果複製到 Excel 上面來加工處理一下,放到 Excel 上的時候我們各放在兩個不同的 Sheet 當中,這裡我先把 Sheet 命名為 Server1 和 Server2

image

 

複製到 Excel 上面之後,我們將這個資料設定為表格,可以選擇上方「插入」→「表格

image

 

此時 Excel 會自動詢問我們資料來源是否正確,沒有問題的話就按下「確定

image

 

因為預設的表格名稱可能不容易辨識,因此可以選擇上方「設計」→「表格名稱」中來做個調整,為了方便確認因此我把兩個表格名稱和 Sheet 的名稱也都設定為相同,分別叫做 Server1 和 Server2

image

 

接著我們在這兩個 Sheet 上面的表格右上方,各分別輸入 lookup 和 compare 來產生兩欄,用來做比對使用

image

image

 

接著我們就可以在第一個Sheet ( Server1 ) 上面的 lookup 欄位中,移到 C2 的位置去輸入公式 「=VLOOKUP([@name],Server2,2,FALSE)」,利用 VLOOKUP 這個函數去指定使用 @name 這個欄位的值,去 Server2 的表格中的第一個欄位去比對資料,帶回第 2 個欄位值的資料回來,比對的時候要完全符合

image

 

至於另外一個 compare 的欄位,我們就多用幾個函數,設定為「=IF( ISNA( [@lookup]),"",[@rows]-[@lookup])」,設定這個欄位的值如果在 lookup 欄位有值的話,則使用 rows 欄位減掉 lookup 欄位的值;沒有值的時候則顯示空白。這樣我們就可以很方便的找出兩邊的差異了

image

 

如果資料太多的話,那也可以在 compare 欄位旁的按鈕上,選擇「數字篩選」→「不等於」,設定條件為不等於 0。

image

image

 

這樣我們就很容易的可以從 Excel 中看出兩邊的差異了,不到幾分鐘就可以完成這個簡單任務了。

 

Server1

image

 

Server2

image

 

後記 : Excel 真的是很方便的工具,上述這些方法是之前所看得一本 Excel 書上所介紹到的方式,有點現學現賣的拿出來使用,有興趣的朋友可以參考「Excel 2013商業智慧分析:資料處理x樞紐分析x Big data分析工具PowerPivot及PowerView」,非常值得推薦的一本好書。