2013/10/22

SQL Serverで大量レコードをインサートする方法

image
SQL Serverに大量データを挿入(インサート)する機会があり、どうすれば効率的にできるか調べてみました。
BCPを使う方法は知っているんですけどね・・・
私はデータベース屋ではないので、もっといい方法があるかもしれません。
2013.10.22 加筆
BULK INSERTに「読み込み開始行(FIRSTROW)」を追記しました。
BULK INSERTの改行文字列の既定値を修正しました。
2015.04.13 加筆
SQL Server PowerShellを使ったBulk Insertの実行サンプル記事のリンクを追記しました。

SQL Serverで大量データをインサート・インポートする方法

いろいろやってみましたが以下の方法がよさそうです。
  1. BULK INSERTステートメント
  2. BCPユーティリティ
  3. SSIS(旧DTS:データ変換サービス)
  4. エクセル+A5:SQL Mk-2を使った手動コピー&ペースト
各方法の詳細は下記の通り。
BULK INSERT(バルク・インサート)ステートメントを使う
BULK INSERTステートメントはテキストファイルを最も高速にインポートできる!と言われています。
理由は簡単!
トランザクションを発行→何回もインサート処理をループさせるよりもコンテキストスイッチの回数が減りますからね。
使い方は通常のINSERTステートメントと同じようにします。
もちろん、実行前にCSVファイルなどを作っておく必要があります。
BULK INSERT テーブル名
FROM 'ファイルのパス'
WITH (FIELDTERMINATOR = '区切り文字', 
    ROWTERMINATOR = '改行文字列',
    FIRSTROW = 開始行番号
)
区切り文字はカンマ(,)やタブ(\t)など。既定は\t
改行文字列は\r\nや\nなど。既定は\r\n SQL Server 2008 R2の規定は\nでした。
1行目がヘッダー行の場合はFIRSTROW = 2にするなど。
詳細はマイクロソフトのMSDNや松本崇博さんのブログを見て下さい。
BCPユーティリティを使う
BCPユーティリティはコマンドライン上から実行します。
バッチだと思えばいいのかな?
これだとバックグランドで実行できるので、ファイルを生成しておき夜間タスクでインサートするような運用で使えますね。
詳しくはマイクロソフトのMSDNやSysteminfinity社のページを見てはどうでしょうか。
SSIS(旧DTS:データ変換サービス)を使う
文字通り、データをインポート・エクスポート・変換ができるSQL Serverの一機能。
SQL Server 2000の時はDTS、SQL Server 2005からはSSIS(SQL Server Integration Services)といいます。
ウィザード形式でもできますし、パッケージも作成できます。
パッケージはプログラムやASP.NETから実行できます。
使い方はgaz76さんのブログや@ITが参考になりますね。
A5:SQL Mk-2とエクセルを使った手動コピペ
エクセルでデータを作成しセルをコピーします。
その後にA5:SQL Mk-2のレコードの貼付け機能を使ってレコードをコピペします。
ちょっとした内容だったらコレが簡単かな~
image

正直、どれをチョイスすればいいかはちょっと悩む・・・

今回の内容を調べ実際に使ってみましたが、正直どれを使えばいいのか悩みました。
リアルタイム性を重視するか、サーバリソースを重視するか・・・
まだまだデータベースに関してはピヨッコ(ひよっこ)なので、いろいろ経験を積んでいくしかないんだろうな~
スポンサーリンク

スポンサーリンク