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

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

# フォーマットファイルを使用するケース 
- INSERT先のテーブルと取込みデータのカラムが一致しない
    - 入れたくないカラムがある場合 ＝ テーブルのカラムをスキップする
    - テーブルにないカラムがデータファイルにある場合 ＝　取込みデータのカラムをスキップする

# 用意するもの
- インポートしたいデータをcsvで用意しておく
- **フォーマットファイル**

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

```sql:フォーマットファイルを出力する構文
bcp [DB名].[所有者].[対象テーブル名] format nul [文字タイプ] [フォーマットファイル出力先] [認証モード]
```

```sql:サンプルコード 
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                            ""
↑　　　　　　　　　　　　　　　　　　　　　　　　　　　　↑
#データファイルのカラム番号                            #テーブルのカラム番号
```

```sql:データサンプル
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                           ""
↑　　　　　　　　　　　　　　　　　　　　　　　　　　　　↑
#データファイルのカラム番号                            #テーブルのカラム番号
```

# あとがき
- とにかく面倒くさいことこのうえない
- 結論としては、なるだけフォーマットファイルを使わないですむような設計を心がけるのが一番