Azure SQL Database (T-SQL) にて２つのテーブルの差分を確認するストアードプロシージャ

## 概要

Azure SQL Database (T-SQL) にて２つのテーブルの差分を確認するストアードプロシージャを共有します。想定結果（`@exp_results_tbl`）は一時テーブルであることが前提です。

## ストアードプロシージャ

### ターゲットテーブルのカンマ区切りのカラム一覧

```sql
CREATE OR ALTER PROCEDURE get_cols_list_from_tmp_tbl @tgt_tmp_tbl_name NVARCHAR(4000)
    ,@cols NVARCHAR(4000) = '' OUTPUT
AS
/*指定した一時テーブルのカラム一覧をリターン

Args:
    @tgt_tmp_tbl_name (nvarhcar): ターゲットの一時テーブル

Returns:
    @cols (nvarhcar): ターゲットテーブルのカンマ区切りのカラム一覧
*/
BEGIN
    DECLARE @defaut_cols NVARCHAR(4000) = @cols
        ,@full_tmp_tbl_name NVARCHAR(4000) = N'tempdb..' + @tgt_tmp_tbl_name
        ,@tmp_cols NVARCHAR(4000)

    DECLARE cols_name_cursor CURSOR LOCAL FAST_FORWARD
    FOR
    SELECT name
    FROM tempdb.sys.columns
    WHERE object_id = object_id(@full_tmp_tbl_name, N'U')

    OPEN cols_name_cursor

    FETCH NEXT
    FROM cols_name_cursor
    INTO @tmp_cols

    WHILE @@FETCH_STATUS = 0
    BEGIN
        IF @cols = @defaut_cols
            SET @cols = @tmp_cols
        ELSE
            SET @cols = @cols + ',' + @tmp_cols

        FETCH NEXT
        FROM cols_name_cursor
        INTO @tmp_cols
    END

    CLOSE cols_name_cursor

    DEALLOCATE cols_name_cursor

    RETURN;
END;
GO
```

### 指定した２つのテーブルの差分がないことを確認。

```sql
CREATE OR ALTER PROCEDURE chekc_tbl_diff_actual_with_expected @exp_results_tbl NVARCHAR(4000)
    ,@act_results_tbl NVARCHAR(4000)
    ,@display_error_records BIT = 0
AS
/*指定した２つのテーブルの差分がないことを確認。

Args:
    @exp_results_tbl (nvarhcar): 想定結果を保持した一時テーブル
    @act_results_tbl (nvarhcar): 確認結果を保持したテーブル
    @display_error_records (bit): 差異の詳細レコードを表示するか

*/
BEGIN
    DECLARE @cols NVARCHAR(4000) = ''
        ,@sql_select NVARCHAR(4000)
        ,@sql_count NVARCHAR(500)
        ,@return_counts INT

    -- `@exp_results_tbl`のカラム一覧を取得
    EXEC get_cols_list_from_tmp_tbl @tgt_tmp_tbl_name = @exp_results_tbl
        ,@cols = @cols OUTPUT

    -- ２つのテーブルの差分を取得する SQL を生成
    SET @sql_select = ''
    SET @sql_select = @sql_select + 'SELECT *'
    SET @sql_select = @sql_select + 'FROM ('
    SET @sql_select = @sql_select + '    SELECT N''' + @act_results_tbl + ' '' AS ut__table'
    SET @sql_select = @sql_select + '        ,' + @cols
    SET @sql_select = @sql_select + '    FROM ' + @act_results_tbl + ''
    SET @sql_select = @sql_select + '    EXCEPT'
    SET @sql_select = @sql_select + '    SELECT N''#ut__src'' AS ut_table'
    SET @sql_select = @sql_select + '        ,' + @cols
    SET @sql_select = @sql_select + '    FROM ' + @exp_results_tbl + ' '
    SET @sql_select = @sql_select + ') act_except_tgt '
    SET @sql_select = @sql_select + 'UNION ALL '
    SET @sql_select = @sql_select + 'SELECT *'
    SET @sql_select = @sql_select + 'FROM ('
    SET @sql_select = @sql_select + '    SELECT N''' + @exp_results_tbl + ''' AS ut__table'
    SET @sql_select = @sql_select + '        ,' + @cols
    SET @sql_select = @sql_select + '    FROM ' + @exp_results_tbl + ' '
    SET @sql_select = @sql_select + '    EXCEPT'
    SET @sql_select = @sql_select + '    SELECT N''#ut__tgt'' AS ut_table'
    SET @sql_select = @sql_select + '        ,' + @cols
    SET @sql_select = @sql_select + '    FROM ' + @act_results_tbl + ''
    SET @sql_select = @sql_select + '    ) tgt_except_act'
    -- ２つのテーブルの差分のレコード数をカウントする SQL を生成して、実行
    SET @sql_count = N'SET @return_counts = (SELECT COUNT(*) AS count  FROM (' + @sql_select + ') src)';

    EXEC sp_executesql @sql_count
        ,N'@return_counts INT OUTPUT'
        ,@return_counts = @return_counts OUTPUT;

    -- 差分のレコード数が 0 ではない場合に、エラー結果をリターン
    IF @return_counts = 0
        BEGIN 
            PRINT ISNULL(OBJECT_NAME(@@PROCID), '') + ': PASSED'
        END
    ELSE
        BEGIN 
            PRINT ISNULL(OBJECT_NAME(@@PROCID), '') + ': NG';
            IF @display_error_records = 1
                EXEC sp_executesql @sql_select;
            THROW 50000
                ,'Test case is not passed.'
                ,1;
        END
END;
GO
```

## 動作確認

### 正常系

```sql

-- 正常系テスト
DROP TABLE IF EXISTS #ut__src
CREATE TABLE #ut__src 
(
    name VARCHAR(10)
    ,num int
    ,test VARCHAR(10)
)
;

DROP TABLE IF EXISTS #ut__tgt
CREATE TABLE #ut__tgt
(
    name VARCHAR(10)
    ,num int
)
;

INSERT INTO #ut__src 
(
    name
)
SELECT 
    *
    FROM (
        SELECT 'A' AS name
        UNION ALL
        SELECT 'B' AS name
        UNION ALL
        SELECT 'C' AS name
    ) src
;

INSERT INTO #ut__tgt 
(
    name
)
SELECT 
    *
    FROM (
        SELECT 'A' AS name
        UNION ALL
        SELECT 'B' AS name
        UNION ALL
        SELECT 'C' AS name
    ) src
;

EXEC chekc_tbl_diff_actual_with_expected
    @exp_results_tbl = '#ut__tgt'
    ,@act_results_tbl = '#ut__src'
    ,@display_error_records = 1

GO
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/f8b71c09-96e9-f3a6-127d-aaa8053be20e.png)


### 異常系

```sql
DROP TABLE IF EXISTS #ut__src
CREATE TABLE #ut__src 
(
    name VARCHAR(10)
    ,num int
    ,test VARCHAR(10)
)
;

DROP TABLE IF EXISTS #ut__tgt
CREATE TABLE #ut__tgt
(
    name VARCHAR(10)
    ,num int
)
;

INSERT INTO #ut__src 
(
    name
)
SELECT 
    *
    FROM (
        SELECT 'A' AS name
        UNION ALL
        SELECT 'B' AS name
        UNION ALL
        SELECT 'C' AS name
    ) src
;

INSERT INTO #ut__tgt 
(
    name
)
SELECT 
    *
    FROM (
        SELECT 'A' AS name
        UNION ALL
        SELECT 'B' AS name
        UNION ALL
        SELECT 'D' AS name
    ) src
;

EXEC chekc_tbl_diff_actual_with_expected
    @exp_results_tbl = '#ut__tgt'
    ,@act_results_tbl = '#ut__src'
    ,@display_error_records = 1
Go
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/ebcea7b3-04c1-e5ed-d060-67716286ba56.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/b162dc8a-469b-cd80-3d91-ee3d81003ea8.png)