SQL Azure - copy table between databases

sql azure azure-sql-database
Alex Dybenko picture Alex Dybenko · Jul 4, 2011 · Viewed 37.5k times · Source

I am trying to run following SQL:

INSERT INTO Suppliers ( [SupplierID], [CompanyName]) 
Select  [SupplierID], [CompanyName] From [AlexDB]..Suppliers

and got an error "reference to database and/or server name in is not supported in this version of sql server"

Any idea how to copy data between databases "inside" the server? I can load data to client and then back to server, but this is very slow.

Answer

Mark Jones picture Mark Jones · Feb 18, 2014

I know this is old, but I had another manual solution for a one off run.

Using SQL Management Studio R2 SP1 to connect to azure, I right click the source database and select generate scripts.

during the wizard, after I have selected my tables I select that I want to output to a query window, then I click advanced. About half way down the properties window there is an option for "type of data to script". I select that and change it to "data only", then I finish the wizard.

All I do then is check the script, rearrange the inserts for constraints, and change the using at the top to run it against my target DB.

Then I right click on the target database and select new query, copy the script into it, and run it.

Done, Data migrated.

hope that helps someone

Related questions

Optimal way to concatenate/aggregate strings

I'm finding a way to aggregate strings from different rows into a single row. I'm looking to do this in many different places, so having a function to facilitate this would be nice. I've tried solutions using COALESCE and FOR …

Read More
Error when connect database continuously

When I am querying from database in continuous looping, after some time I get an error : An exception has been raised that is likely due to a transient failure. If you are connecting to a SQL Azure database consider using …

Read More
Why is running a query on SQL Azure so much slower?

I created a trial account on Azure, and I deployed my database from SmarterAsp. When I run a pivot query on SmarterAsp\MyDatabase, the results appeared in 2 seconds. However, running the same query on Azure\MyDatabase took 94 seconds. I use …

Read More