Sql-server – Migrating data from SQLEXPRESS local DB to SQL Azure

azuresql serversql-server-2012

I have a process that is importing data from CSV files to a local SQL Express DB, then I can use the SQL Server 2012 migration tool to migrate the database to SQL Azure (as described in http://bit.ly/WRPSZs). It works great for moving a database.

However, it's a bit of a pain when the data changes and I just want to freshen the DB. I tried changing my scripts that do this to just connect to the SQL Azure DB, but ran into the problem that SQL Azure apparently doesn't support BCP:

Msg 40526, Level 16, State 1, Server Xxxxxx, Line 37
'BULK' rowset provider not supported in this version of SQL Server.

So I'm back to importing to SQL Express, then dropping the DB, exporting it from SQL Express and then going and recreating the DB users and roles.

Anyone have a better way?

Best Answer

According to the Azure documentation you should be able to import data using the BCP command. see: BCP and SQL Azure

To confirm that it was working I also went and tested it out. For my test I inserted 10 rows of data from my csv file called import.txt. I used the -t, syntax to specify that it was comma delimited.

Here's the (redacted) syntax of the command that I used: 

bcp database_name.schema.table in C:\import.txt -c -U username@servername -S servername.database.windows.net -P password -t,

From here I could see the following results

Starting copy...

10 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 390    Average : (25.64 rows per sec.)

I have seen a few instances where inserting a very large amount of data can cause the import to fail but that would probably be a different error than what you're seeing.

Related Topic