How to join two tables from different database in MS SQL Azure?

Question

I have two different tables in two different databases. Need to join both the table and get results using query

1) reports - report_id, report_name, description

table reports is present in database 'A'

2) report_owners - report_id, report_owner

table report_owners is present in database 'B'

tried following query for joining the tables but it's throwing error ( Reference to server and/ database is not supported in this version of SQL server).

Query:

select * from [A].[dbo].['reports'] as all_reports
INNER JOIN
select * from [B].[dbo].['report_owners'] as all_owner
ON all_report.report_id = all_owners.report_id

Note: from Database 'A' have access for cross query functionality for database 'B'

Anyone please help me to solve this issue?

Possible duplicate of [Can't query between databases in SQL Azure](https://stackoverflow.com/questions/11284998/cant-query-between-databases-in-sql-azure) — ahmed abdelqader, May 16 '19 at 08:58

score 2 · Answer 1 · edited Sep 19 '20 at 07:07

You need to use elastic queries to perform cross database queries. See the following example:

Customerinformation (remote db)

CREATE TABLE dbo.CustomerInformation (CustomerID int identity(1,1) , CustomerName nvarchar(255));

DECLARE @i int = 1;
WHILE @i < 20000
BEGIN
INSERT INTO CustomerInformation(CustomerName)
VALUES ('Frodo');

SET @i += 1;
END

On the database that will use the external table

CREATE TABLE OrderInformation(OrderId Int identity(1,1), CustomerId int,Ordervalue int);


SET NOCOUNT ON;
DECLARE @i int = 1;
WHILE @i < 200 
BEGIN
INSERT INTO OrderInformation(CustomerId,Ordervalue)
Values (@I,111);

set @i += 1;
END

CREATE EXTERNAL TABLE  [dbo].CustomerInformation(
    CustomerID [int],
CustomerName nvarchar(255))
    WITH  
(  
    DATA_SOURCE = Stackoverflow  
);

Query the remote table like this.

SELECT o.OrderId, c.CustomerID, c.CustomerName
  FROM OrderInformation o
  JOIN CustomerInformation c ON o.CustomerID = c.CustomerID
 WHERE o.OrderId = 155

How to join two tables from different database in MS SQL Azure?

1 Answers1