First published on MSDN on Jan 19, 2019
Hello,
This week I worked a service request when our customer reported
performance issues
obtaining data from
External Tables
using Azure SQL Database. They followed our article about how to configure it.
I suggested to use Cross Database Queries in the similar way that we have in SQL Server using Azure SQL Managed Instance. I provided an example to do it, even using a distributed transaction.
[code language="SQL"]
-- ==============================
-- CREATE DATABASE
-- ==============================
CREATE DATABASE DatabaseA
GO
CREATE DATABASE DatabaseB
GO
-- ===============================
-- Connected to the database B, create table and insert some data
-- ===============================
USE DatabaseB
go
create table TableExample (ID INT, Name VARCHAR(20))
GO
INSERT INTO TableExample values(1,'Data in DBB')
GO
-- ===============================
-- Connected to the database A, create table and insert some data
-- ===============================
USE DatabaseA
GO
create table TableExample (ID INT, Name VARCHAR(20))
go
INSERT INTO TableExample values(1,'Data in DBA')
GO
SELECT * FROM DatabaseB.dbo.TableExample
-- ===============================
-- Also, it is possible to use distributed query running in the same transaction.
-- ===============================
BEGIN TRANSACTION
INSERT INTO DatabaseB.dbo.TableExample values(2,'Data in DBB')
INSERT INTO DatabaseA.dbo.TableExample values(2,'Data in DBA')
COMMIT TRANSACTION
SELECT * FROM DatabaseB.dbo.TableExample
SELECT * FROM DatabaseA.dbo.TableExample
[/code]
Enjoy!!
