Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Azure - copy table between databases

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.

like image 205
Alex Dybenko Avatar asked Jul 04 '11 14:07

Alex Dybenko


People also ask

How do I copy a table from one SQL database to another?

Right-click on the database name, then select "Tasks" > "Export data..." from the object explorer. The SQL Server Import/Export wizard opens; click on "Next". Provide authentication and select the source from which you want to copy the data; click "Next". Specify where to copy the data to; click on "Next".

How do I transfer data from Azure SQL to Azure SQL?

In the Azure portal menu, select All services. Search for and select Azure Database Migration Services. On the Azure Database Migration Services screen, select the Azure Database Migration Service instance that you created. Select New Migration Project.


1 Answers

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

like image 130
Mark Jones Avatar answered Oct 11 '22 12:10

Mark Jones