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 — ahmed abdelqader
– ahmed abdelqader, Commented May 16, 2019 at 8:58

Martijn Pieters · Accepted Answer · 2020-09-19 07:07:41Z

2

Answer recommended by Microsoft Azure Collective

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

edited Sep 19, 2020 at 7:07

Martijn Pieters

1.1m326 gold badges4.2k silver badges3.4k bronze badges

answered May 16, 2019 at 11:03

Alberto Morillo

15.8k3 gold badges29 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related