Not able to do cross database query in SQL Azure

Question

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.

Answer 1

Yes, you can use the Elastic Query Features on SQL Azure.It's the only way you can perform the cross database Queries.

Here are the detailed Queries to follow:

Run the below Query in your DB1(Since you said like reading the TB1 from DB2 and insert those Data's into your TB2 in your DB1)

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'STro*ngPaSSe0rD';

CREATE DATABASE SCOPED CREDENTIAL Login
WITH IDENTITY = 'Login',
SECRET = 'STro*ngPaSSe0rD';

CREATE EXTERNAL DATA SOURCE RemoteReferenceData
WITH
(
    TYPE=RDBMS,
    LOCATION='myserver.database.windows.net',
    DATABASE_NAME='DB2',
    CREDENTIAL= Login
);




CREATE EXTERNAL TABLE [dbo].[TB1]
(
    [Columns] [DataTypes] 
)
WITH (DATA_SOURCE = [RemoteReferenceData])

After these step, you can Query the external table like the Normal table. Though some limitations while using the External table, like you couldn't able to Insert Data's into a EXTERNAL TABLE(Reference table)

Answer 2

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).