How to import bulk data in Azure SQL

Background

Its a common scenario for sql server based application to facilitate import/export data from various sources. With sql server import/export wizard it was so easy, that it never been a concern, but when you try to do the same for azure sql environment, that’s a different story.

Problem

When importing bulk data, azure sql has some limitation. It do not facilitate bulk data import yet. So when I was in need of importing 2 million+ records to my Sql DB in azure, I was in trouble.

Few common options were

  1. generating data script from local sql server and run the same in azure
  2. from local sqlserver run a bulk insert query directly to azure sql

both options are miserably failed, as sqlserver started to show out of memory exception due to volume of data. One workaround is to spill data in small chunks and insert into diff temporary table, then generate data script from those table. But I do not like these kind of workaround anyway, because of the nature of effort and re-usability.

Solution

Solution was rather easy, have to use a command-line tool called The bcp utility. The bcp  client is installed when you install Microsoft SQL Server tools.

My requirement was to export 2millon+ data from local sql server, where database name is Enrollment and table name Enrollments

Local Sql Server Azure Sql
Database Enrollment azuredb
Table Name Enrollments Enrollments
Db UserName N/A username
Db pass N/A password

From command prompt and enter the following line of code:

BCP <databasename>.dbo.<tablename> OUT <localfolder>\<filename>.txt -T -c

According to my example:

BCP Enrollment.dbo.Enrollments OUT E:\BulkInsert.txt -T -c
You will see a nice text file generated with all data from the targeted table, now its time for import to azure sql

 BCP <databasename>.dbo.<tablename> IN <localfolder>\<filename>.txt -S <servername>;
 -U <username>@<servername> -P <password> -c

According to my example:

BCP azuredb.dbo.Enrollments IN E:\BulkInsert.txt -S myazure.database.windows.net; -U username@myazure.database.windows.net -P password -c

Entire process take few minutes, with simple bcp utility the import/export became a life saver for large bulk data manipulation. And with a batch file this can be turned to a reusable one too.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s