How do I create a view using existing tables from another database just like using dblinks?

Question

Hi experts,

Would like to kindly ask for help with creating a view using a query that selects from tables from another database in the same server. Thanks!

Answer 1

Hi,

The solution is to use EXTERNAL table

let's assume that you want to select data from Tbl01 in DB01 from DB02, then

(1) Connect DB02

(2) Create master key if does not exists

(3) Create DATABASE SCOPED CREDENTIAL which point to a user in DB01

(4) Create an EXTERNAL DATA SOURCE to DB01

(5) Create EXTERNAL TABLE with the exact same structure as Tbl01 which point to Tbl01

That's it... you can now use the external table and get the data from the external DB01

Here is a full demo

------------------ Connect master

CREATE DATABASE db01(EDITION = 'Basic', SERVICE_OBJECTIVE = 'Basic')
GO

CREATE DATABASE db02(EDITION = 'Basic', SERVICE_OBJECTIVE = 'Basic')
GO

------------------ Connect db01 - this database is the source table
create table Db1Tbl (id int, txt nvarchar(100))
GO

INSERT Db1Tbl(id, txt) values (1,'ronen'),(3, 'ariely')
GO

------------------ Connect db02 - from here we will use the table in the other db
-- Create master key for database if not exists
CREATE MASTER KEY ENCRYPTION BY PASSWORD='Not!My@Password';
GO
-- enter credentials to a login in the Azure SQL Database db01
-- DROP DATABASE SCOPED CREDENTIAL AppCredential
CREATE DATABASE SCOPED CREDENTIAL AppCredential 
    WITH IDENTITY = 'Your_User_name', SECRET = 'Your_Password'
GO

-- DROP EXTERNAL DATA SOURCE RemoteReferenceData
CREATE EXTERNAL DATA SOURCE RemoteReferenceData
WITH(TYPE=RDBMS,
    LOCATION='Server_Name.database.windows.net',
    DATABASE_NAME='db01',
    CREDENTIAL= AppCredential
);
GO

-- DROP EXTERNAL TABLE Db01Tbl
CREATE EXTERNAL TABLE Db01Tbl(id int, txt nvarchar(100))
WITH(DATA_SOURCE = RemoteReferenceData,
    SCHEMA_NAME = N'dbo',  
    OBJECT_NAME = N'Db1Tbl')
GO

SELECT * FROM Db01Tbl
GO

Answer 2

As far as I know, accessing tables/views from another Sql Azure database is not supported. Unless it is hosted on a VM like normal Sql Server. I might be wrong here