BULK INSERT文を利用してAzure SQL Databaseへデータ追加するSQLファイルを作成し実行してみた

BULK INSERT文を利用したAzure SQL Databaseへのデータ追加は、以下の記事のように実施できる。

BULK INSERT文を利用してCSVファイルのデータをAzure SQL Databaseのテーブルに追加してみたBULK INSERT文を利用すると、以下のサイトのように、CSVファイルのデータをSQL Serverのテーブルに追加することができる...

ただ、SQLファイルに上記処理を記載しておくと、一括でBULK INSERT文を利用したAzure SQL Databaseへのデータ追加を行うことができる。

今回は、BULK INSERT文を利用してAzure SQL Databaseへデータ追加するSQLファイルを作成し実行してみたので、そのサンプルプログラムと実行手順を共有する。

前提条件

以下の記事に従って、Azure SQL Database・Azure Blob Storageの作成が完了していること。

Azure Potal上でSQLデータベースを作成してみたAzure Portal上では、常に最新の安定したバージョンの SQL Serverデータベースである「SQL データベース」が利用でき...

Azure Blob Storageを作成しファイルを格納してみたAzure Blob Storageを利用すると、Azure上にBlobデータ(テキストファイルや画像、アーカイブファイル等)を格納する...

また、A5M2からAzure SQL Databaseに接続できていること。その手順は、以下の記事を参考にすること。

A5M2を利用して各DBに接続してみたA5M2(A5:SQL Mk-2)は、SQL文の入力支援やER図作成などの機能を備えていて、Oracle、MySQL、PostgreSQ...

SQLファイルの作成

作成したSQLファイル(bulk_insert_user_data.sql)の内容は、以下の通り。

-----------------------------------------------------
-- 前処理
-----------------------------------------------------
-- 定数宣言
DECLARE @master_key  NVARCHAR(20)  = '(何らかのパスワード)';
DECLARE @blob_secret NVARCHAR(200) = '(BLOB SAS トークンの値)';
DECLARE @blob_url    NVARCHAR(100) = 'https://azureblobpurinit.blob.core.windows.net/work';

-- 変数宣言
DECLARE @create_master_key NVARCHAR(100);
DECLARE @db_scoped_cred    NVARCHAR(1000);
DECLARE @create_ext_ds     NVARCHAR(1000);

-- マスターキーの作成
SET @create_master_key = 'CREATE MASTER KEY ENCRYPTION BY PASSWORD =' + CHAR(39) + @master_key + CHAR(39);
EXEC(@create_master_key);

-- 資格情報の作成
SET @db_scoped_cred = 'CREATE DATABASE SCOPED CREDENTIAL AzureBlobPurinitCredential001
                         WITH IDENTITY = ''SHARED ACCESS SIGNATURE''
                            , SECRET = ' + CHAR(39) + @blob_secret + CHAR(39);
EXEC(@db_scoped_cred);

-- 外部データソースの作成
SET @create_ext_ds = 'CREATE EXTERNAL DATA SOURCE AzureBlobPurinitWorkDs001
                      WITH ( TYPE = BLOB_STORAGE
                           , LOCATION = ' + CHAR(39) + @blob_url + CHAR(39) + '
                           , CREDENTIAL = AzureBlobPurinitCredential001 )';
EXEC(@create_ext_ds);

-- 作成したマスターキー・資格情報・外部データソースを使えるようにするため、処理を確定させる
GO

-----------------------------------------------------
-- データ追加・確認
-----------------------------------------------------
-- 定数宣言
DECLARE @csv_file_name NVARCHAR(20) = 'test.csv';

-- 変数宣言
DECLARE @bulk_insert   NVARCHAR(1000);

-- BULK INSERT文の実行前確認
SELECT * FROM dbo.USER_DATA ORDER BY id ASC;

-- BULK INSERT文の実行
SET @bulk_insert = 'BULK INSERT
                       dbo.USER_DATA
                    FROM ' 
                      + CHAR(39) + @csv_file_name + CHAR(39) + '
                    WITH (
                        DATA_SOURCE = ''AzureBlobPurinitWorkDs001''
                      , FORMAT = ''CSV''
                      , FIRSTROW = 1
                      , DATAFILETYPE = ''CHAR''
                      , CODEPAGE = ''65001''
                   )';
EXEC(@bulk_insert);

-- BULK INSERT文の実行後確認
SELECT * FROM dbo.USER_DATA ORDER BY id ASC;

-- 次処理で作成したマスターキー・資格情報・外部データソースを削除するため、処理を確定させる
GO

-----------------------------------------------------
-- 後処理
-----------------------------------------------------
-- 外部データソースの削除
DROP EXTERNAL DATA SOURCE AzureBlobPurinitWorkDs001;

-- 資格情報の削除
DROP DATABASE SCOPED CREDENTIAL AzureBlobPurinitCredential001;

-- マスターキーの削除
DROP MASTER KEY;

SSMSによるSQLファイルの実行

SSMSによるSQLファイルの実行結果は、以下の通り。

1) SSMSでAzure SQL Databaseにログインし、以下のように、コンソールにSQLファイルの内容を貼り付けた状態で「Execute」ボタンを押下する。

2) SQLが実行でき、以下のように、BULK INSERT前後のSELECT結果が表示されることが確認できる。

なお、SSMSのオプションは、以下のデフォルト（オートコミットされるモード）の設定で実行している。

sqlcmdコマンドによるSQLファイルの実行

sqlcmdコマンドによるSQLファイルの実行結果は、以下の通り。

1) コマンドプロンプトを起動し、sqlcmdコマンドの配置されているディレクトリに移動する。

cd C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn
dir

　なお、以下の「SQLCMD.EXE」が、sqlcmdコマンドになる。

2) 以下のように、user_dataテーブルを更新するためのSQLファイルを、「C:\tmp\bulk_insert_user_data.sql」に配置する。

3) sqlcmdコマンドを利用して、3)のSQLファイルの内容に従って、bulk insertを行う。

sqlcmd -S azure-db-purinit.database.windows.net -U purinit@azure-db-purinit -P (パスワード) -d azureSqlDatabase -i "C:\tmp\bulk_insert_user_data.sql" -o "C:\tmp\bulk_insert_user_data.log"