An Azure relational database service.
Hi @Gowri Shankar , thanks for replying back.
Yes cross-database queries is supported in Azure SQL and known as Elastic query, we can query cross multiple databases in same server or databases across different servers as well. Please check the sequence of steps I followed:
- Created first SQL Database server Customers with username 'admin' and password 'password@123'
- Created another SQL Database server Orders with username 'admin' and password 'password@123'(You can choose different username and password here)
- Login to Orders database and run below query: CREATE TABLE [dbo].OrderInformation
INSERT INTO [dbo].[OrderInformation] ([OrderID], [CustomerID]) VALUES (123, 1)
INSERT INTO [dbo].[OrderInformation] ([OrderID], [CustomerID]) VALUES (149, 2)
INSERT INTO [dbo].[OrderInformation] ([OrderID], [CustomerID]) VALUES (857, 2)
INSERT INTO [dbo].[OrderInformation] ([OrderID], [CustomerID]) VALUES (321, 1)
INSERT INTO [dbo].[OrderInformation] ([OrderID], [CustomerID]) VALUES (564, 8) - Login to Customers Database run below query: CREATE TABLE [dbo].CustomerInformation
INSERT INTO [dbo].[CustomerInformation] ([CustomerID], [CustomerName], [Company]) VALUES (1, 'Jack', 'ABC')
INSERT INTO [dbo].[CustomerInformation] ([CustomerID], [CustomerName], [Company]) VALUES (2, 'Steve', 'XYZ')
INSERT INTO [dbo].[CustomerInformation] ([CustomerID], [CustomerName], [Company]) VALUES (3, 'Lylla', 'MNO') - Again go back to Orders database and run below query: CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password@rahul kumar ';
CREATE DATABASE SCOPED CREDENTIAL ElasticDBQueryCred
WITH IDENTITY = 'admin',
SECRET = 'password@123';
Please note the identity and password we use here is same what we used for Customers database. If the passwords don't match we get the exact same error mentioned.
6). Create external data source in Orders database as below:
CREATE EXTERNAL DATA SOURCE MyElasticDBQueryDataSrc WITH
(TYPE = RDBMS,
LOCATION = '<server_name>.database.windows.net',
DATABASE_NAME = 'Customers',
CREDENTIAL = ElasticDBQueryCred,
) ;
7). Create external data table in Orders database as below:
CREATE EXTERNAL TABLE [dbo].[CustomerInformation]
( [CustomerID] [int] NOT NULL,
[CustomerName] [varchar](50) NOT NULL,
[Company] [varchar](50) NOT NULL)
WITH
( DATA_SOURCE = MyElasticDBQueryDataSrc)
8). Then we can execute the sample elastic query in Orders database as below:
SELECT OrderInformation.CustomerID, OrderInformation.OrderId, CustomerInformation.CustomerName, CustomerInformation.Company
FROM OrderInformation
INNER JOIN CustomerInformation
ON CustomerInformation.CustomerID = OrderInformation.CustomerID
Reference Article: Get started with cross-database queries (vertical partitioning) (preview)
Please let me know if this helps or else we can discuss further on the same.
----------
If answer helps you, you can mark it 'Accept Answer'