3
Help us understand the problem. What are the problem?

More than 1 year has passed since last update.

posted at

[SQL Server] Bulk Insertでデータインポート(フォーマットファイル使用)

はじめに

前回、大量データを定義通りにインポートする方法を投稿したけれど、たまにこのカラムは入れたくない等のケースがある
そういう場合はフォーマットファイルを使わないといけない
これがなかなか面倒なのでまとめてみる
[SQL Server] Bulk Insertでデータインポート(フォーマットファイルなし)

フォーマットファイルを使用するケース

  • INSERT先のテーブルと取込みデータのカラムが一致しない
    • 入れたくないカラムがある場合 = テーブルのカラムをスキップする
    • テーブルにないカラムがデータファイルにある場合 = 取込みデータのカラムをスキップする

用意するもの

  • インポートしたいデータをcsvで用意しておく
  • フォーマットファイル

フォーマットファイルの作り方

1. bcpでテーブルからフォーマットファイルを出力する

フォーマットファイルを出力する構文
bcp [DB].[所有者].[対象テーブル名] format nul [文字タイプ] [フォーマットファイル出力先] [認証モード]
サンプルコード
bcp TEST.dbo.tbl_test format nul -c -f C:\test\test.fmt -T
  • 文字タイプ: -c
  • フォーマットファイルの出力先: -f フルパス
  • 認証モード: -T windows認証
    • sqlserver認証の場合はユーザー名パスワードが必要
test.fmt

13.0 #バージョン
5 #データファイルのカラム数              
1       SQLCHAR             0       8       "\t"     1     sequenceNumber                     Japanese_CI_AS
2       SQLCHAR             0       13       "\t"    2     corporateNumber                    Japanese_CI_AS
3       SQLCHAR             0       2       "\t"     3     process                            Japanese_CI_AS
4       SQLCHAR             0       12      "\r\n"   4     correct                            ""
↑                            ↑
#データファイルのカラム番号                            #テーブルのカラム番号
データサンプル
sequenceNumber  process correct
1                01     1
2                01     1
3                01     1
  • テーブルのカラムとデータファイルのカラムが一致しないケースは成型する必要あり
    • データファイルにはcorporateNumber列がない
データファイル テーブル
カラム カラム番号 カラム番号
sequenceNumber 1 1
corporateNumber -- 2
process 2 3
correct 3 4

2. フォーマットファイルをテーブル定義にあわせて成型する

2_1. 入れたくないカラムがある場合 = テーブルのカラムをスキップする

  • スキップするテーブルのカラムをフォーマットファイルから除外
  • テーブルのカラム番号を調整する
  • データファイルのカラム数を変更する
  • 改行文字: \nに変更する
データファイル テーブル
カラム カラム番号 カラム番号
sequenceNumber 1 1
process 2 3
correct 3 4
成型後のフォーマットファイル
13.0 #バージョン
4 #データファイルのカラム数              
1       SQLCHAR             0       8       "\t"     1     sequenceNumber                    Japanese_CI_AS
2       SQLCHAR             0       2       "\t"     3     process                           Japanese_CI_AS
3       SQLCHAR             0       12      "\r\n"   4     correct                           ""
↑                            ↑
#データファイルのカラム番号                            #テーブルのカラム番号

2_2. テーブルにないカラムがデータファイルにある場合 = 取込みデータのカラムをスキップする

  • データファイルのスキップするカラムに対応するテーブルのカラム番号を0に変更する
  • 改行文字: \nに変更する

(ex)データファイルのcorrectをテーブルに入れたくない

データファイル テーブル
カラム カラム番号 カラム番号
sequenceNumber 1 1
process 2 0
correct 3 4
成型後のフォーマットファイル
13.0 #バージョン
4 #データファイルのカラム数              
1       SQLCHAR             0       8       "\t"     1     sequenceNumber                    Japanese_CI_AS
2       SQLCHAR             0       2       "\t"     0     process                           Japanese_CI_AS
3       SQLCHAR             0       12      "\r\n"   4     correct                           ""
↑                            ↑
#データファイルのカラム番号                            #テーブルのカラム番号

あとがき

  • とにかく面倒くさいことこのうえない
  • 結論としては、なるだけフォーマットファイルを使わないですむような設計を心がけるのが一番
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Sign upLogin
3
Help us understand the problem. What are the problem?