I am migrating all my VM
Sql Server 2012 database to Azure Sql Database. In my current structure I am using cross database queries to fetch data from different database tables.
I have created external table to my parent table using below query
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'yourPassword'; CREATE DATABASE SCOPED CREDENTIAL yourServeradminlogin WITH IDENTITY = 'yourServeradminlogin', SECRET = 'yourPassword'; CREATE EXTERNAL DATA SOURCE RefmyDemoDB2 WITH ( TYPE=RDBMS, LOCATION='testdbdemoserver.database.windows.net', DATABASE_NAME='myDemoDB2', CREDENTIAL= yourServeradminlogin ); CREATE EXTERNAL TABLE [dbo].[Department]( [DeptId] [int] NOT NULL, [Name] [varchar](50) NULL ) WITH ( DATA_SOURCE = RefmyDemoDB2 ); /****** Script for SelectTopNRows command from SSMS ******/ SELECT * FROM [dbo].[Employee] E INNER JOIN [dbo].[Department] D ON E.DeptId = D.DeptId
I referred this link https://www.c-sharpcorner.com/article/cross-database-queries-in-azure-sql/
In my case it directly showing in Tables folder.
Anyone knows why I don't see Department table in
External Tablesfolder? How can I add such tables in
External tables are available in Azure SQL only to support a feature called "Elastic Queries", that may solve your problem:
If this is not enough for you, and you really need full cross-database query support, you have to use an Azure SQL Managed Instance:
which seems to be exactly what you need.