「風と雲と～」＜違っ
さて、素朴な疑問、といいますか、業務で使うあるフローの実現においてふと思った事があるのです。
つまり、BULK INSERTによる読み込みでファイルの行順番は保証されているのだろうか、ということ。
まぁなんでそんなしょうも無い事の壁に当たってしまったのかは後述。
で、実験です。
1000万件のレコードファイルを準備します。レコードは２フィールド、中身は１～の連番数字を二つ。これをキーを持たない２フィールドのカラムを持つ空のテーブルにBULK INSERTします。その後、そのテーブルにIDテーブルを付加し、そのIDキーとフィールドの連番数字が一致するのかどうかを見てみよう、というわけです。
drop table test1
go
create table test1 ( item1 bigint not null,item2 bigint not null )
go
bulk insert test1 from 'c:\fortest\rectxt.txt' with ( FIELDTERMINATOR = ',')
go
alter table test1 add keys bigint identity (1,1) not null
go
select count(*) from test1 where keys <> item1
go
そもそも論として、「だったらテーブルにID列もっとけばすむじゃん」と思うかもしれません。確かに BULK INSERT でもINSERT先にID列が存在していれば、賢いSQL-Serverはその列をすっ飛ばしてキチンとデータをINSERTしてくれます。けれど、この処理が成立できるには、テーブルのフィールド数＝ファイルレコードデリミタ数＋1、という法則が必要。つまり、一列IDENTITYであるということを考えた上で、フィールドデリミタを１個付加する必要があるんです。けれど数十万レコードのCSVファイルの編集処理をかました上でそれをするとなると……（遠い目）
「この面倒臭がりが。フォーマットファイル作れよ」という話もありますが、最近、せきゅりてーせきゅりてーと変な風に突っ走ってるユーザーさんは、コンピューターにファイルが入るのを嫌がりよるんです。「ファイル増えるの？ それってウイルス大丈夫なの？ だれが面倒みるのよ」とごねられちゃうと、営業さんは戦う事も出来ずに泣いて現場という名のバショクをきるわけです。そんなもんなの、デスマーチの前兆なんて＜おい
というわけで、現場ってそうそううまくはできてません。特に、他部署や他会社から渡されたファイルを自システムで処理する、なんて案件はザラっと転がっていて、その相手が全く違うプラットホームのシステム生成だったりするとこちらの思ったようなデータファイルがそうそううまく来てくれるわけもなく、「こうしてくれよ～」的な希望だって大人の事情で通るとも限らないわけだったりするんです。そこで、ファイルの内容はそのままでINファイルとして生かすためのBULK INSERT、自システムで扱いやすくするためのALTER TABLE だったりするんですね。BULK前にファイルを読み込んでデリミタ付加していくのもありなんですけど……そのためだけにファイル処理かますのもいやだし……どうしてWindowsにはsedが無いんだぁっ！（と世界の中心で愛を叫ぶsed&awkまにあ）
はぁ……気を取り直して、さて、テストテスト♪
１回目 10,000,000件中 441,728件相違……（－－；むむっ、 これはバッチサイズ指定無し＝10,000,000件一括コミット
２回目 10,000,000件中 9,996,192件相違……（ＴдＴ） がぁぁっ！ バッチサイズ=1000の結果
３回目 10,000,000件中 9,996,192件相違……ええええええ？ １回目と同じＳＱＬ文やねんけど？
４回目 10,000,000件中 0件相違……エエカンジ！ １回目、３回目と同じＳＱＬでテーブル名を新規のものに変更
５回目 10,000,000件中 9,997,416件相違……１回目と同じＳＱＬ。どーゆーこと？
<<---------- ここでトランザクションログを使い切り再起動 ----------------------------------------------->>
６回目 10,000,000件中 0件相違……１回目のテーブルと同一レイアウトの一時テーブルを作成して同じ処理。
７回目 10,000,000件中 10,000,000件相違…… orz ６回目と同じＳＱＬ。同じデータベースクエリ画面上。
８回目 10,000,000件中 10,000,000件相違……６回目と同じＳＱＬ。新規のデータベースクエリ画面上。
９回目 10,000,000件中 10,000,000件相違……４回目と同じＳＱＬ。新規のデータベースクエリ画面上。
10回目 10,000,000件中 0件相違……４回目と同じＳＱＬ。新規のデータベースクエリ画面上。
11回目 10,000,000件中 0件相違……４回目と同じＳＱＬ。新規のデータベースクエリ画面上。作成先は一時テーブル。
……わけわかんねーっ！法則性もなんもねーっ！
もっと不可解なのは３回目と５回目。１回目と同じＳＱＬでバッチサイズは指定していないのだけれど、なぜか２回目と近い、つーかほぼ同じ値が出てきてます。もしかしてSQL-Serverってとっても賢くて、「前はこういうBULKしてたから今回もかな～」なんてお気楽に「小さな親切よけいなお世話」モード発令してんじゃね？的な穿つ気持ちがふつふつと（おい）
そしてテストして気づいた事。SQL Server Management Stadio のデータベースクエリを使ってSQL文を実行していたわけですが、一時テーブルのオブジェクトは「その一時テーブルを作ったSQL文を実行したシート」上では保持されていたんですね。つまり、データベースクエリを実行した.sqlの画面を閉じない限りはそのシート内で一時テーブルの内容も参照できるし、オブジェクトが存在している事もtempdb上で確認できました。
でも、欲しい結論は結局出ず……
やりたかった事：フォーマットファイルを使わずにファイルのレコード順序を保持してBULKINSERTしたかった
結論：あきらめてフォーマットファイル使え
って事で……営業さん、泣いて（おい）
あくまでも経験則な自分（笑）
補足：フォーマットファイルをつかってみた
実験でやること：
1000万件データファイルをフォーマットファイルつかってID列をつけながらBULK INSERTしてみる
対象テーブル：hoge(id bigint identity(1,1) not null, hoge1 bigint not null, hoge2 bigint not null)
フォーマットファイル：hoge1（テーブルの２番目） と hoge2（テーブルの３番目） のみを SQLCHARモード、カンマ区切り、改行区切りで読み込み
batchsize=1
確実に一致（そりゃそーだ）
batchsize=100
まだ一致（おお、がんばる）
batchsize=1000
まだ一致（やるねぇ～）
batchsize=10000
まだ一致（おっ！）
batchsize=100000
まだ一致（ををっ！）
batchsizeなし
おっしゃぁぁっ！
ってことで、SQL-Server的には、ファイルを一行読んで、フォーマットにあわせてテーブルに入れて、IDを一個増やして、を繰り返し、batchsizeタイミングの件数でCommitしてるんだろうなぁ、これって。ってことでやりたい事はこれでOK
結論：ファイルの読み込み順を保持してBULK INSERTする場合には、ID列を付与したファイルレコードで読むか、なければフォーマットファイルを使ってID列を付与したテーブルへ処理するか、どちらかにしとけ
そして次の問題
デリミタは送り側で気まぐれに変えてくるので、フォーマットファイルでのデリミタも動的でなくてはならない
あぁぁぁぁ……（奈落の底へ落ちていくの図）