一人アドカレ
| 日 | ポスト |
|---|---|
| 1 | 一時テーブルが存在していたら削除する |
| 2 | 何度も使う中間テーブルに名前をつけてクエリを見やすくする |
| 3 | 共通テーブル式と一時テーブルの参照可能な範囲の違い |
| 4 | グループ毎に最大値や最小値を持つレコードを抽出する |
| 5 | |
| 6 | SQL Serverのデータインポートウィザードを使って日時データをインポートする |
| 7 | 再帰共通テーブル式で連続した値を得る |
| 8 | 拡張プロパティに入っているテーブルやカラムの説明を取得し表示する |
| 9 | |
| 10 | |
| 11 | |
| 12 | |
| 13 | |
| 14 | |
| 15 | |
| 16 | |
| 17 | |
| 18 | |
| 19 | |
| 20 | |
| 21 | |
| 22 | |
| 23 | |
| 24 | |
| 25 |
BULK INSERTを使ってcsvを一括登録する
課題
csvデータを直接テーブルへ登録する。
準備
実行環境
- SQL Server 2017 Standard
- SQL Server Management Studio 2017
サンプルデータ
登録するcsvデータ : fruits.csv
id,name,price
1,apple,100
2,banana,
3,orange,200
登録先のテーブル : fruitsテーブル
| 列名 | データ型 | NULL制約 |
|---|---|---|
| id | int | NULL 不可 |
| name | varchar(10) | NULL 不可 |
| price | int | NULL 可 |
解法
ここではfruits.csvを C:\temp\ へ置いているとする。
BULK INSERT [fruits]
FROM 'C:\temp\fruits.csv'
WITH ( DATAFILETYPE = 'char'
, FIRSTROW = 2
, FIELDTERMINATOR = ','
, KEEPNULLS );
GO
上記のコードの実行結果は次の通り。
| id | name | price |
|---|---|---|
| 1 | apple | 100 |
| 2 | banana | NULL |
| 3 | orange | 200 |
上記コードのざっくりした説明
BULK INSERTは次のような構文になっている。
BULK INSERT [①テーブル名]
FROM '②csvファイルの絶対パス'
WITH ( ③インサートするための設定 );
①テーブル名にはcsvのデータを挿入したいテーブル名を指定する。
②csvファイルの絶対パスはその表記通り、テーブルへ挿入するデータの記載されたcsvファイルの絶対パスである。
③インサートするための設定はWITH句を使って記載するが、このWITH句は先日Qiitaへ投稿した共通テーブル式とは異なる。ここでは「BULK INSERTの設定はこうだよ」ということを示すための使い方になる。
今回の例で設定したのは DATAFILETYPE , FIRSTROW , FIELDTERMINATOR , KEEPNULLS の4つで、それぞれのもつ意味は次の通り。
- DATAFILETYPE
- 読み込むデータの形式を指定する。charは文字形式。その他にUnicode形式やネイティブ形式がある。規定はchar。
- FIRSTROW
- 読み込み始める行番号を指定する。今回の例だと2行目からデータとして読み込む。 ヘッダを読み飛ばすとかそういった意味合いを持つものではなくて、単純に読み込み始める行番号を指定するものなので注意する。
- FIELDTERMINATOR
- データを区切る文字を指定する。今回の例だと,を指定している。csvですし。
- KEEPNULLS
- これを記載すると、データが空の列にはNULLが挿入されるようになる。記載しない場合、既定値が入る。
その他にも設定できることがあるため、参考のリンクも参照ください。
KEEPNULLについての補足
例えば、fruitsテーブルのname列のNULL制約ををNULL可にし、既定値として Def_Val を設定した上で、次のcsvをBULK INSERTすることを考える。
id,name,price
1,apple,100
2,banana,
3,,200
最後の行のnameが空になったcsvだ。
このとき、KEEPNULLSを指定しないでBULK INSERTすると以下のような結果になる。
| id | name | price |
|---|---|---|
| 1 | apple | 100 |
| 2 | banana | NULL |
| 3 | Def_Val | 200 |
空の列には既定値が使用される。
一方で、KEEPNULLSを指定すると次のようになる。
| id | name | price |
|---|---|---|
| 1 | apple | 100 |
| 2 | banana | NULL |
| 3 | NULL | 200 |
空の列でも既定値は使われず、NULLとなった。
感想
csvのフォーマットが決まりきっているときはよく使っています。便利なので、機会があればご一考ください。