Not able to do cross database query in SQL Azure

user_2781180 0
Viewed 16926

I have 2 DB on same SQL Azure server and i have same table(TB1) on both DB, now i want to read data from TB1 of DB2 and insert data into TB1 of DB1. I am using below query but getting error.

insert into TB1 select 1,* from [DB2].dbo.TB1

Error Message

Msg 40515, Level 15, State 1, Line 16

Reference to database and/or server name in 'DB2.dbo.TB1' is not supported in this version of SQL Server.

azure-sql-database
3 Answers

Azure supports this cross database query feature since 2015 but needs some extra setup to work and Elastic Query.

The first step is create a security credential:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';
CREATE DATABASE SCOPED CREDENTIAL DB2Security 
WITH IDENTITY = '<username>',
SECRET = '<password>';

The "username" and "password" should be the username and password used to login into the DB2 database.

Now you can use it to define a external datasource, so DB1 can connect to DB2:

CREATE EXTERNAL DATA SOURCE DB2Access
WITH (
    TYPE=RDBMS,
    LOCATION='myservernotyours.database.secure.windows.net',
    DATABASE_NAME='DB2',
    CREDENTIAL= DB2Security);

Finally, you map the TB1 as a external table from the DB2 database, using the previous external datasource:

CREATE EXTERNAL TABLE dbo.TB1FromDB2(
    ID int,
    Val varchar(50))
WITH
(
    DATA_SOURCE = DB2Access);

You can also accomplish this using the Azure SQL Data Sync, but the data are replicated in one single database and this feature are still a preview version (May/2018) and you always see oldest data (the minimal configurable interval for each synchronization is 5 minutes).

user_23879770
Related