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

SomebodyHelpMePlease 26 Reputation points
2022-03-25T06:54:08.513+00:00

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!

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,139 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,586 questions
0 comments No comments
{count} votes

Accepted answer
  1. Ronen Ariely 15,091 Reputation points
    2022-03-25T08:56:32.09+00:00

    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
    
    1 person found this answer helpful.

1 additional answer

Sort by: Most helpful
  1. Ali Sufyan Butt 86 Reputation points MVP
    2022-03-25T15:35:55.673+00:00

    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