本エントリはVisual Studio / Visual Studio Code Advent Calendar 2015 14日目のエントリです
SQL Server Data Tools に含まれる「データ比較」ツール
SQL Server Data Tools(SSDT)に含まれる機能の一つに、『データ比較』ツール というものがあります。
SQL Server Data Tools自体がどんなものかは、ググっていただくか、以前中国地方DB勉強会でお話しさせていただいた際のこちらの資料を参照いただければと思います
今回はこの『データ比較』ツール についてざっくりと紹介してみようと思います。
「データ比較」ツール とは
その名のとおり、複数のデータベーススキーマにある同一のテーブルのデータ同士を比較し、
- 差分の検出
- 差分をグラフィカルに表示
- 差分を解消する更新を行うクリプトの生成
- 差分を解消する更新の即時実行
等をシームレスに行うことができます
スクリーンショットとともに、少し例をお見せしたいと思います
データの準備
今回はSQL Server LocalDB上に、UserとBookの二つのテーブルを持ったデータベースを二つ用意します なお、Userのデータはなんちゃって個人情報で適当に生成したもので、BookのデータはAmazonのランキングから適当に引っ張ってきたものです
デーベースその１：SampleDb01_Develop
Userテーブル：
Bookテーブル：
デーベースその１：SampleDb01_Staging
Userテーブル：
Bookテーブル：
行数もカラム数も少ないのでかろうじて目diffできないこともない…ですが、まあやりたくないですよね
データ比較ツールの起動
データ比較ツールの起動方法はいくつかあるのですが、今回はSQL Server エクスプローラのDBを選択しコンテキストメニューから起動してみます。
データベースとオプションの選択
データ比較のダイアログが表示されます。前述の方法で起動すると、すでにソースデータベースは選択されているので、比較対象のデータベースを「ターゲットデータベース」側に選択します。 ドロップダウンに出てこない場合は「新しい接続」ボタンからダイアログを起動して設定します。なお、ソースデータベースとターゲットデータベースは、真ん中にある矢印のボタンで簡単に入れ替えることができます。
データ比較のオプションは、検出する比較する差分を選択します。不要なものは除外しておくほうがたぶん早くなるので、検出したい差分が明確な場合は（例えば「追加になったデータだけ検出したい」など）必要なもののみに絞るとよさそうです。
オブジェクトの選択
次に、比較するオブジェクトを選択します。それぞれのスキーマから検出された比較可能なテーブルとビューが一覧されますので、実際に比較をしたいオブジェクトを選択します。
比較結果の確認
しばらくすると（データの量などで時間は変わります）、結果ビューが表示されます。
今回の場合は以下のようになっています
- Book → ターゲット内にのみにある行を検出
- User → 同一キーだがデータが異なる行、ソース内のみにある行、ターゲット内のみにある行を検出
ここで、グリッドのそれぞれのセルを選択すると、詳細が下側のペインに表示されます。 例えば Userテーブルの「異なるレコード」 をクリックすると、こんな感じで交互にカラムを並べて差分のカラムを太文字で表示してくれます。これだと違いが分かりやすいですね。
それぞれ一方にしかない行の場合はそのまま差分の行が表示されます。
更新の実行またはスクリプトの生成
結果ビューのグリッドで最終的に「更新」にチェックが入ったものに対して、ターゲット側を更新するためのスクリプトを生成したり、そのまま更新を実行したりできます。
例えば先ほど結果を全チェックした状態で「スクリプトの生成」を実行すると、以下のようなSQLが生成されます。
/* このスクリプトは 2015/12/15 の 0:08 に Visual Studio によって作成されました。 このスクリプトを (localdb)\v11.0.SampleDb01_Staging (XXX\xxxxx ) で実行すると、(localdb)\v11.0.SampleDb01_Develop (XXX\xxxxx) と同じにできます。 このスクリプトは次の順にアクションを実行します: 1. 外部キー制約を無効にします。 2. DELETE コマンドを実行します。 3. UPDATE コマンドを実行します。 4. INSERT コマンドを実行します。 5. 外部キー制約を再び有効にします。 このスクリプトを実行する前に、ターゲット データベースをバックアップしてください。 */ SET NUMERIC_ROUNDABORT OFF GO SET XACT_ABORT , ANSI_PADDING , ANSI_WARNINGS , CONCAT_NULL_YIELDS_NULL , ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON GO /*テキストまたはイメージの更新に使用されるポインターです。これは不要である可能性がありますが、念のためここで宣言されています*/ DECLARE @pv binary (16) BEGIN TRANSACTION DELETE FROM [dbo].[User] WHERE [Id]= 10 DELETE FROM [dbo].[User] WHERE [Id]= 11 DELETE FROM [dbo].[Book] WHERE [Id]= 4 UPDATE [dbo]. [User] SET [Birthday] ='20150514' WHERE [Id]= 1 UPDATE [dbo]. [User] SET [Birthday] ='20151116' WHERE [Id]= 5 UPDATE [dbo]. [User] SET [Name] =N'黒川 佑' WHERE [Id]= 7 INSERT INTO [dbo].[User] ([Id], [Name] , [Birthday], [Email]) VALUES ( 2, N'奈良 希', '20150920', N' nara_nozomi@example.com' ) INSERT INTO [dbo].[User] ([Id], [Name] , [Birthday], [Email]) VALUES ( 3, N' 岡田 光臣' , '20150928' , N' okada_mitsuomi@example.com' ) INSERT INTO [dbo].[User] ([Id], [Name] , [Birthday], [Email]) VALUES ( 4, N'相武 美智子', '20150326', N' aibu_michiko@example.com' ) COMMIT TRANSACTION
もしくは「ターゲットの更新」ボタンをクリックして実行することで、上記スクリプトを生成後即実行します。
まとめ
いかがでしょうか？ 開発環境やステージング環境など同一のスキーマ構造のデータベース間でデータの差分を確認したり調整を行うことはちょくちょくあるのではないかと思います。そういった際には非常に便利なツールです。 またSSDTが持つ別の機能「スキーマ比較」と合わせて利用すると、複数の環境を管理する際にずいぶん楽ができるようになるのではなと思います。
このSSDTに関する機能、正直日本語での情報が多いとは言えません。 気になった方はぜひ触ってみて、blogなどで情報を発信してもらえると私がとても喜びますのでぜひよろしくお願いします。
