More than 3 years have passed since last update.

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

SQLServer

Posted at 2020-04-16

はじめに

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