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

Question

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?

If you're doing this a lot you should consider putting all tables in the same database in a different schema — Nick.Mc
– Nick.Mc, Commented Aug 17, 2020 at 23:06
I guess in some applications that would be appropriate. What we have here are mirrored staging and production environments so separate databases seems more appropriate. Sometimes though we need to push staged data through to production and up until now it's been very cumbersome. I intend to script this; create the link, publish the data and then drop the linkage so that the two databases are once again separate once the operation is complete. — Yossi G.
– Yossi G., Commented Aug 17, 2020 at 23:22
Have you considered building this process in data factory? Then all of your ETL is outside the database and you don't have to run any scripts against your prod database at all. ADF is great for metadata driven operations, i.e. tell it a table and it does it all without requiring column mappings etc. — Nick.Mc
– Nick.Mc, Commented Aug 18, 2020 at 1:01

David Browne - Microsoft · Accepted Answer · 2020-08-17 22:48:52Z

6

Answer recommended by Microsoft Azure Collective

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'  
)

answered Aug 17, 2020 at 22:48

David Browne - Microsoft

90.7k7 gold badges52 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Yossi G. Over a year ago

Quick follow-up - will a stored procedure transaction successfully span both databases?

David Browne - Microsoft Over a year ago

No. See: learn.microsoft.com/en-us/azure/azure-sql/database/…

bruce neiman Feb 25 at 22:29

We were struggling to setup Azure Sync due to FW issues and dealing with so many DB's via the UI. Azure Elastic Query was way simpler to setup and more flexible.

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related