Reputation: 510

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.

Upvotes: 38

Answers (12)

Mark Jones

Reputation: 1491

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.

Upvotes: 84

Greg R Taylor

Reputation: 3676

You can easily add a "Linked Server" from SQL Management Studio and then query on the fully qualified table name. No need for flat files or export tables. This method also works for on-prem to azure database and vice versa.

e.g.

select top 1 ColA, ColB from [AZURE01_<hidden>].<hidden>_UAT_RecoveryTestSrc.dbo.FooTable order by 1 desc

select top 1 ColA, ColB from [AZURE01_<hidden>].<hidden>_UAT_RecoveryTestTgt.dbo.FooTable order by 1 desc

Upvotes: 1

hui chen

Reputation: 1074

I would recommend SSMS SQL Server Import and Export feature. This feature supports multiple connection configurations and cross-server copy of selected tables. I have tried .NET Sql Server connector, which works very well for the Azure SQL databases.

Upvotes: 1

Markus

Reputation: 4149

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

Upvotes: 36

Thomas

Reputation: 116

If you have onprem version that has the sp_addlinkedsrvlogin, you can setup Linked Servers for both source and target database then you can run your insert into query.

See "SQL Server Support for Linked Server and Distributed Queries against Windows Azure SQL Database" in this blog: https://azure.microsoft.com/en-us/blog/announcing-updates-to-windows-azure-sql-database/

Upvotes: 2

Vignesh M

Reputation: 178

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.

Upvotes: 3

Vad

Reputation: 898

A few options (rather workarounds):

Generate script with data
Use data sync in Azure
Use MS Access (import and then export), with many exclusions (like no GUID in Access)
Use 3-rd party tools like Red Gate.

Unfortunately no easy and built-in way to do that so far.

Upvotes: 0

Terence Siganakis

Reputation: 519

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

Upvotes: 9

ozz

Reputation: 5366

An old post, but another option is the Sql Azure Migration wizard

Upvotes: 3

Alex Dybenko

Reputation: 510

Ok, i think i found answer - no way. have to move data to client, or do some other tricks. Here a link to article with explanations: Limitations of SQL Azure: only one DB per connection But any other ideas are welcome!

Upvotes: 1

Richard Mitchell

Reputation: 421

You could use a tool like SQL Data Compare from Red Gate Software that can move database contents from one place to another and fully supports SQL Azure. 14-day free trial should let you see if it can do what you need.

Full disclosure: I work for Red Gate Software

Upvotes: 3

IUnknown

Reputation: 898

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

Upvotes: 4

SQL Azure - copy table between databases

Answers (12)

Related Questions