SQL ServerにCSVファイル or Excelファイルからデータをインポートする際に注意（NOT NULLと改行）すること。

SQL Serverで開発システムの場合、テスト環境を構築する際に手っ取り早くデータをいれるときはManagement Studioのデータインポート機能を使うと思います。

そこで久々に使ったんですけど、ちょっとはまったので今後も忘れないように記録しておきます。

たいていは設計時にねたをExcelにまとめていると思うのでそこからデータインポートすると思います。

その際、Excelをそのまま入れるかCSV(フラットファイル)にして入れるかでそれぞれ注意するべきところが違うことが分かりました。

varcharなどでNOT NULL制約があるカラムに対してExcelのセルが空欄のままだとNULLをいれるようとしてこけます。
この場合、空欄セルにデータで使われていない文字（★など）をいれといて入れた後にupdateで空文字にしましょう。
以下のようなSQLです。

update hogehoge
set col = ''
where col = '★'

セル内に改行があると失敗します。この場合、Excelで改行をまずデータで使われていない文字（■など）に置換して入れた後に改行に置換します。
Excelの改行コードは置換のときにCtrl+Jキーを押すと入力できます。見えませんけど。
以下のようなSQLです。

update hogehoge
set col = replace(col, '■', CHAR(13) + CHAR(10))

な気がします。なのでエラーメッセージが何を言っているかわからない場合はCSVインポートに切り替えてみるのも一つの手です。
CSVでもそれほど親切なエラーメッセージはでませんが。

CSVでもこれは同じです。

1項目が50文字以上あるはCSV取り込み時の詳細設定で文字数を変更する必要があります。
なぜかデフォルトすべて50になってました。
これに気付かずにテーブルより桁数が少ないのに切り捨てましたみたいなエラーがでてはまりました。

上記のような感じなのでおすすめは50文字以上のデータがあるテーブルはExcelで、それ以外はCSVで試して最終的にだめならCSVでエラーをつぶす。ですがね。

ちなみにこれはSQL Server 2008*1の場合ですので最近のバージョンだと違うかもしれません。というか改善されていてほしい。

*1:なんでこんな10年前ほどのバージョンを使っているかというと顧客指定だからです

Code for final