0

I have a use case where I want to be able to insert certain data rows from one database environment to another.

I have the grasp for how to copy from one database to another while on the same server by:

INSERT DB1.dbo.DataValue(DateStamp, ItemId, Value)
SELECT DateStamp, ItemId, Value 
FROM DB2dbo.DataValue AS t
WHERE NOT EXISTS (SELECT 1 FROM DB1.dbo.DataValue AS d
WHERE DateStamp = t.DateStamp
AND ItemId = t.ItemId);

But I am unsure about how to do it over databases hosted on different servers. Ignoring the security aspect for now as well. Database version is: Microsoft SQL Azure (RTM) - 12.0.2000.8

Any help would be greatly appreciated! Thanks!

Dale K
  • 25,246
  • 15
  • 42
  • 71
MAK7
  • 125
  • 1
  • 12
  • 1
    You can't do cross database queries Azure SQL Database, let alone Cross Server queries. You would, however, have to use the same method as a cross database solution, and use an `EXTERNAL DATA SOURCE`. – Thom A Feb 10 '21 at 17:41
  • Yes thank you! I then utilised a script to copy data for this use case. – MAK7 Feb 15 '21 at 12:02

1 Answers1

1

To copy data between different servers you can use:

  1. Linked servers (NOT supported in Azure. thanks to a comment)
  2. Any ETL-tool,e.g. SQL Server Integration Services
Sergey
  • 4,719
  • 1
  • 6
  • 11
  • 1
    Linked servers aren't supported in Azure SQL Databases. – Thom A Feb 10 '21 at 17:42
  • Have you looked at this question https://stackoverflow.com/questions/4526461/converting-select-results-into-insert-script-sql-server. It has a highly voted answer that mending some manual steps but it will definitely work for without needing too much effort. – vvs Feb 10 '21 at 17:55