Bulk Load Files into SQL Azure?

16
votes

I have an ASP.NET app that takes multimegabyte file uploads, writes them to disk, and later MSSQL 2008 loads them with BCP.

I would like to move the whole thing to Azure, but since there are no "files" for BCP, can anyone comment on how to get bulk data from an Azure app into SQL Azure?

I did see http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx but am not sure if that applies.

Thanks.

4
azurebulkinsertazure-sql-databasebcp
Loading data to SQL Azure the fast way – Mitch Wheat
If the uploads are from an ASP.NET web page you will hit the same problems I have if the data is larger than 30 MB, see my question stackoverflow.com/questions/6402253/… for more information. – user152949
Please note that this article was written on an earlier architecture behind SQL Azure. Today, it does not quite work the same way (in the DTU or vCore models). Bulk Copy is a more appropriate way to try to load data quickly into SQL Azure since it reduces round trips, groups writes into larger transactions, and takes more extensive locks when inserting to avoid having to do the overhead of locking each and every row being inserted. – Conor Cunningham MSFT
Hi @ Conor Cunningham MSFT : Is there any updated MS article available? I'm currently using SqlBulkCopy exclusively to load data quickly into an Azure SQL Database. – Mitch Wheat
I will go look through the content and see if we have something more current. I co-authored a white paper awhile back on this but it may also have aged out. Things move more quickly in the cloud ;). Bulk insert is the fastest way to load into SQL Azure, however, but there are nuances around batch/txn size, how close the client is to SQL Azure, and whether you need to bulk insert into a permanent table vs. a temp table (latter is much faster to insert in premium/v-core models since it does not have to replicate across nodes on commit). – Conor Cunningham MSFT

4 Answers

7
votes

BCP is one way to do it.

This post explains it in three easy steps: Bulk insert with Azure SQL

3
votes

You are on the right track. The Bulk Copy API will work. I am using it. And it's the fastest way to import data because it uses INSERT BULK statements. Not to get confused with the BULK INSERT statement which is not supported in SQL Azure. In essence, BCP and the SqlBulkCopy API use the same method.

1
votes

I think it's important to note that BCP cannot handle source files that are Unicode while using a format file to do your import.