I was about to upload 2 million record of data using NHibernate’s default save method, which will transact every record independently. It estimated almost 2 days to upload all data.
In this mass data upload, bulk import utility, bcp is pretty useful. It takes 10 minutes for 2 million record to import.
Related well written article.
http://lostechies.com/jimmybogard/2010/06/24/bulk-processing-with-nhibernate/
And sample console commands,
Example for importing CSV to SQL
bcp [db name] in [absolute path to source file, e.g. CSV] -S [sever address] -t , -U [user id] -P [password] -f [absolute path to format file]
Creating Format File
bcp [db name] format nul -f [format file name] -c -t , -S [server name] -U [user id] -P [password]
-t , : define delimiter as “,”
-f : format file is optional
By utilizing format file, I could remove unwanted quotation (“”) from the value. Format file looks ugly but, it works and useful. Better to know it. Without understanding format file, it’s almost hard to import real work requirement.
Tips: Be aware of line change types among windows, lynux and OS-X.