SQL Azure - copy table between databases

Question

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.

Solution 1

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

Solution 2

Since 2015, this can be done by use of elastic database queries also known as cross database queries.

I created and used this template, it copies 1.5 million rows in 20 minutes:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';

CREATE DATABASE SCOPED CREDENTIAL SQL_Credential  
WITH IDENTITY = '<username>',
SECRET = '<password>';

CREATE EXTERNAL DATA SOURCE RemoteReferenceData
WITH
(
    TYPE=RDBMS,
    LOCATION='<server>.database.windows.net',
    DATABASE_NAME='<db>',
    CREDENTIAL= SQL_Credential
);

CREATE EXTERNAL TABLE [dbo].[source_table] (
    [Id] BIGINT NOT NULL,
    ...
)
WITH
(
    DATA_SOURCE = RemoteReferenceData
)

SELECT *
 INTO target_table
FROM source_table

Solution 3

Unfortunately there is no way to do this in a single query.

The easiest way to accomplish it is to use "Data Sync" to copy the tables. The benefit of this is that it will also work between servers, and keep your tables in sync.

http://azure.microsoft.com/en-us/documentation/articles/sql-database-get-started-sql-data-sync/

In practise, I haven't had that great of an experience with "Data Sync" running in production, but its fine for once off jobs.

One issue with "Data Sync" is that it will create a large number of "sync" objects in your database, and deleting the actual "Data Sync" from the Azure portal may or may not clean them up. Follow the directions in this article to clean it all up manually:

https://msgooroo.com/GoorooTHINK/Article/15141/Removing-SQL-Azure-Sync-objects-manually/5215

Solution 4

Use the following steps, there is no straight forward way to do so. But by some trick we can.

Step1 : Create another one table with the same structure of Suppliers table inside [AlexDB], Say it as SuppliersBackup

Step2 : Create table with the same structure of Suppliers table inside DesiredDB

Step3 : Enable Data Sync Between AlexDB..Suppliers and DesiredDB..Suppliers

Step4 : Truncate data from AlexDB..Suppliers

Step5 : Copy data from AlexDB..SuppliersBackup to AlexDB..Suppliers

Step6 : Now run the sync

Data Copied to DesiredDB.

Solution 5

SQL-Azure does not support USE statement and effectively no cross-db queries. So the above query is bound to fail.

If you want to copy/backup the db to another sql azure db you can use the "Same-server" copying or "Cross-Server" copying in SQL-Azure. Refer this msdn article

View more solutions

Comments

[Alex Dybenko]

Thanks, but i need to copy one table, from one DB to another. Any hints?

[David Steele]

Do you need to do this once, on a scheduled regular basis e.g every night or on demand?

[Alex Dybenko]

well, this is part of big "job" - I compare table structure to other database and adjust it accordingly, and here is need to copy data from backup database to new one

[IUnknown]

Ok, sounds like a case for SQL Azure Sync (Currently in CTP). You can configure code-independent sync relationship between your sql azure databases using Sync. More details on Sync Framework Team Blog blogs.msdn.com/b/sync/archive/2011/03/08/…

[pim]

If you're simply looking to copy DATA. Then this is the right answer.

[Mark Jones]

after 32 up votes it is a shame this has not been accepted as the answer

[Dustin Metzgar]

Thanks! In my case I needed to do a point in time restore, delete all the data I don't want and then follow your instructions to get a script to apply to the original.

[buttonupbub]

Thanks for this method. Effective when working with databases on Azure.

[SUMguy]

tried this but I just get incorrect syntax near 'DATA_SOURCE' - tried adding a ; but same message

[Markus]

@james5 To help you I need to see more of your code, maybe you create a new stackoverflow question.

[Peter Ellis]

Unless I misunderstand, this method requires the data to be small enough that it's feasible to store it all in one script and copy and paste it. Isn't this just another way of downloading it to the client and uploading it again to the target database?

[Mark Jones]

Correct. As I said at the beginning of the comment "a manual solution for a one off run". I know it is not perfect but it solved my problem at the time.

[Yster]

This code is incomplete: target_table isn't even defined.

[Yster]

Sync only works if the table is less than 500 MB in size!

[gpanagakis]

@SUMguy remove all primary key and other constraints only WITH and DATA_SOURCE argument