SQL Azure - copy table between databases

user_193074 0
Viewed 46897

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.

azure-sql-database azure sql
12 Answers

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

Management Studio Screenshot

user_41339480

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.

user_92045630
Related