はじめに
弊社では、DBの一部にSQL Serverを使用しています。
開発時に大量データの登録を一括で行う場合におすすめしたいBULK INSERTについてご紹介したいと思います。
BULK INSERTとは
CSV形式などの形式で、テーブルに一括でデータを登録できるコマンドです。
MicrosoftのSQLドキュメントのBULK INSERTのリンク
データ登録してみる
まずBULK INSERTがどんなものかを知るために、簡易的なテーブルにデータ登録してみます。
利用するテーブル
| 説明 | カラム名 | 型 |
|---|---|---|
| ID | ID | int |
| 名前 | Name | nvarchar(50) |
| 体重 | Weight | int |
| 身長 | Height | int |
| データ登録日 | RegistDT | datetime |
User.sql
CREATE TABLE [dbo].[User](
[Id] [int] NULL,
[Name] [nvarchar](50) NULL,
[Weight] [int] NULL,
[Height] [int] NULL,
[RegistDT] [datetime] NULL
) ON [PRIMARY]
登録データ
ヘッダー行は不要のため1行目から登録データを記述し、ここではセパレータは半角カンマ（,）を利用します。なおデータ内に半角カンマ（,）が含まれている場合のエスケープ方法は分かりませんでしたので、セパレータにはタブ（\t）を利用してみてください。
BULK_INSERT_USER.txt
1,pique,80,194,2019/08/19 8:01:59
2,messi,72,170,2019/08/19 8:01:59
3,suarez,86,182,2019/08/19 8:01:59
MSサイトではヘッダーについてこのように記載されています。
※FIRSTROW=2を指定することで対処可能だったりもします。
ヘッダーのスキップは、BULK INSERT ステートメントではサポートされません。
BULK INSERTクエリ
テーブルと登録データの準備が整ったので、BULK INSERTしてみます。
BULK_INSERT_USER.sql
BULK INSERT [User]
FROM 'C:\work\BULK_INSERT_USER.txt'
WITH
(
FIELDTERMINATOR =','
)
--BULK INSERT結果確認
SELECT * FROM [User] WITH (NOLOCK)
オプション引数
上記ではオプション引数はFIELDTERMINATORのみ利用ましたが、いくつかご紹介します。
| 名称 | 説明 |
|---|---|
| FIELDTERMINATOR | セパレータを指定します。 半角カンマ（,）やタブ（\t）などで、規定値はタブ（\t）です。 |
| DATAFILETYPE | 登録データの型に応じて'char','native','widechar','widenative'のいずれかを指定します。既定値は'char'です。詳細はこちらを参照。 |
| KEEPIDENTITY | ID（IDENTITY）列がある場合に指定することで、自動発番を無視して登録データの値で登録します。 |
| KEEPNULLS | 登録データ上にNULLが存在する場合に指定し、NULLは削除します。詳細はこちらを参照 |
| ROWTERMINATOR | 行の終端セパレーターを指定します。LFの場合は'0x0A'を指定します。詳細はこちらを参照 |
サンプル
私が利用しているBULK INSERTクエリのサンプルをご紹介します。
このサンプルでは、テスト用にBEGIN TRANコマンドで検証し、ID（IDENTITY）列を無視してデータを登録を行います。
BULK_INSERT_USER.sql
SELECT COUNT(*) FROM [User] WITH (NOLOCK) --BULK INSERT前の件数
BEGIN TRAN BULK_INSERT_USER
BULK INSERT [User]
FROM 'C:\work\BULK_INSERT_USER.txt'
WITH
(
FIELDTERMINATOR =','
,KEEPIDENTITY --自動発番を無視する場合には指定
)
ROLLBACK TRAN BULK_INSERT_USER --テスト時に利用するためロールバックする
--COMMIT TRAN BULK_INSERT_USER
SELECT COUNT(*) FROM [User] WITH (NOLOCK) --BULK INSERT後の件数
最後に
データ登録作業は頻繁に行う作業ではないため、いざ作業をしようと思うとオプションを忘れていたり、BULK INSERTコマンドのエラーメッセージが分かりづらかったりと、思うように作業が進まないことがありまとめました。少しでも皆さんの作業が楽になれば何よりです。