{"id":724,"date":"2012-10-30T21:32:32","date_gmt":"2012-10-30T11:32:32","guid":{"rendered":"http:\/\/www.moneystock.net\/wp_e\/?p=724"},"modified":"2012-11-01T21:47:36","modified_gmt":"2012-11-01T11:47:36","slug":"bulk-data-import-to-mssql","status":"publish","type":"post","link":"https:\/\/moneystock.net\/wp_e\/2012\/10\/30\/bulk-data-import-to-mssql\/","title":{"rendered":"Bulk data import to MSSQL"},"content":{"rendered":"

I was about to upload\u00a02 million\u00a0record of data using NHibernate’s default save method, which will transact every record\u00a0independently. It estimated almost 2 days to upload all data.<\/p>\n

In this mass data upload, bulk import utility, bcp is pretty useful. It takes 10 minutes for 2 million record to import.<\/p>\n

Related well written article.<\/p>\n

http:\/\/lostechies.com\/jimmybogard\/2010\/06\/24\/bulk-processing-with-nhibernate\/<\/p>\n

And sample console commands,<\/p>\n

Example for importing CSV to SQL<\/strong>
\nbcp [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]<\/p>\n

Creating Format File<\/strong>
\nbcp [db name] format nul -f [format file name] -c -t , -S [server name] -U [user id] -P [password]<\/p>\n

-t , : define delimiter as “,”<\/p>\n

-f : format file is optional<\/p>\n

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.<\/p>\n

Tips: Be aware of line change types among windows, lynux and OS-X.<\/p>\n

 <\/p>\n","protected":false},"excerpt":{"rendered":"

I was about to upload\u00a02 million\u00a0record of data using NHibernate’s default save method, which will transact every record\u00a0independently. 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… Continue reading Bulk data import to MSSQL<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[128],"tags":[],"class_list":["post-724","post","type-post","status-publish","format-standard","hentry","category-db","entry"],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t