SQL Server: Data Compare using SSDT and Visual Studio 2015

一. 前言:

因為最近工作的需求, 有不同 DB 上 Tables 的內容比對. 故以一個簡單的環境實作, 並整理如下:

二. 環境:

(一) Visual Studio 2015: 必須安裝 SQL Server Data Tools (SSDT), 由此下載.

(二) SQL Server 2014 現有資料: 相關的 SQL Script 可參考 六. 附錄

     1.. 來源資料庫: Test02

          (1) Table: Customers

          (2) Table: Departments

          (3) Table: Employees

     2.. 目標資料庫: Test02_Backup

          (1) Table: Customers

          (2) Table: Departments

          (3) Table: Employees

     3.. 差異:

          (1) Test02.Customers 與 Test02_Backup.Customers 資料內容完全相同

          (2) Test02.Departments 比 Test02_Backup.Departments 多了一筆資料

          (3) Test02.Employees 與 Test02_Backup.Employees 在第1筆資料的 address 欄位有差異: "taipei" vs. "taiwan"

三. 實地操作: (in Visual Studio 2015)

     1.. 新增資料比較 ...

     2.. 選取連線

     3.. 選擇要比對的資料表 (注意: 只有 Primary Key 的資料表, 且相同資料表名稱才會出現在這個頁面)

     4.. 查看結果:

          (1) Customers:

          (2) Departments:

          (3) Employees:

四. 延伸閱讀:

今日(2017.01.17) 利用 SQL Server Profiler 針對比對的過程作記錄, 發現 SSDT 是將資料整個取回到用戶端才作比對, 如下圖. 故若用戶端的機器等級不是很高, 而資料量很大, 可能要花一些時間作比對.

如果想要讓比對的工作在 DB Server 執行後, 才將結果回傳, 請使用 EXCEPT 指令. 範例指令如下:

SELECT *
FROM Test02.dbo.Employees
EXCEPT
SELECT *
FROM Test02_Backup.dbo.Employees
;

SELECT *
FROM Test02_Backup.dbo.Employees
EXCEPT
SELECT *
FROM Test02.dbo.Employees
;

但缺點是, 無法明確指出是那個欄位不同, 如下圖; 這個部份, SSDT 的 UI 作得比較好

五. 參考文件:

1.. Compare and Synchronize Data in One or More Tables with Data in a Reference Database

n this section, you can find descriptions of common tasks that support this scenario.

Set options to control how the data is compared: When you compare data, you can safely ignore identity columns, disable triggers, and disable foreign keys. You can also drop primary keys, indexes, and unique constraints from the update script.

Compare data in tables and optionally update the target to match the source: After you specify a source and a target database to compare and run the comparison, you can view the results in the Data Compare window.

2.. How to: Use Schema Compare to Compare Different Database Definitions

六. 附錄:

(一) Customers:

CREATE TABLE [dbo]. [Customers] (

     [id] [int] NOT NULL,

     [name] [nvarchar] (20 ) NULL,

     [address] [nvarchar] (60 ) NULL,

  CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED (id)

)

GO

-- for DB: Test02 & Test02_Backup

INSERT INTO [dbo] .[Customers] ([id] , [name] , [address]) VALUES ( 1 , N'SYSCOM', N'taipei')

(二) Departments:

CREATE TABLE [dbo]. [Departments] (

     [id] [int] IDENTITY( 1, 1) NOT NULL,

     [name] [nvarchar] (20 ) NULL,

  CONSTRAINT [PK_Department] PRIMARY KEY CLUSTERED (id)

)

GO

-- for DB: Test02

SET IDENTITY_INSERT [dbo]. [Departments] ON

INSERT INTO [dbo]. [Departments] ( [id], [name]) VALUES (1 , N'HR')

SET IDENTITY_INSERT [dbo]. [Departments] OFF

(三) Employees:

CREATE TABLE [dbo]. [Employees] (

     [id] [int] NOT NULL,

     [name] [nvarchar] (20 ) NULL,

     [address] [nvarchar] (60 ) NULL,

     [tel] [varchar] (20 ) NULL,

  CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED (id)

)

GO

-- for DB: Test02

INSERT INTO [dbo]. [Employees] ( [id], [name], [address], [tel]) VALUES (1 , N'jasper', N'taipei' , N'12345678' )

-- for DB: Test02_Backup

INSERT INTO [dbo]. [Employees] ( [id], [name], [address], [tel]) VALUES (1 , N'jasper', N'taiwan' , N'12345678' )