CODEWITHSUNDEEP
sql-serverazure-sql-database
Yossi G.
Yossi G.

Reputation: 1153

How do I link two tables of the same name across two Azure SQL Server databases?

I have used Scoped Database Credentials, External Data Sources and External Tables in the past to build links in one database to tables in a separate database. However, up to this point, I have only found this to work if the table names are different (either by name or by schema) in both databases. This is because the linkage expects that the name of the external table as defined in the 'source' database is going to exactly match the name of the table in the 'target' database.

What I have is two databases which are 'mirror images' of each other. Each database has a table named [dbo].[Customers]. In DatabaseA, which already has a table named [dbo].[Customers], how can I link to [dbo].[Customers] in DatabaseB? SQL won't allow me to build an EXTERNAL table named [dbo].[Customers] in DatabaseA because it already exists. I can build an EXTERNAL table named [ext].[Customers] but then that doesn't map to the table in DatabaseB because there is no [ext].[Customers] in DatabaseB.

So this is what my question boils down to - is there a way to create an EXTERNAL table named [ext].[Customers] in DatabaseA and map it to [dbo].[Customers] in DatabaseB?

Upvotes: 2

Views: 1071

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 88861

You can specify the remote schema and object name in CREATE EXTERNAL TABLE, eg

CREATE EXTERNAL TABLE ext.Customers
(   
  [CustomerID] int NOT NULL,
  [CustomerName] varchar(50) NOT NULL,
  [Company] varchar(50) NOT NULL
)
WITH
( 
  DATA_SOURCE = MyElasticDBQueryDataSrc,
  SCHEMA_NAME = N'dbo',  
  OBJECT_NAME = N'Customers'  
)

Upvotes: 5

Related Questions