4

I want to be able to run some ad-hoc queries to get some fast results. The following will return the number of rows in table foobar from two databases that have identical structures.

USE Master
GO
select count(*) from MyFirstDB.dbo.foobar;
select count(*) from MySecondDB.dbo.foobar;

This works fine for SQL Server, but SQL Azure returns errors. I read that you with SQL Azure you cannot change the database context in the query window of SSMS. Is there a way to make this work? What happens if I want to create a join across two databases?

WhiskerBiscuit
  • 4,795
  • 8
  • 62
  • 100
  • For the cross db queries: https://azure.microsoft.com/en-gb/blog/querying-remote-databases-in-azure-sql-db/ – cp50 Dec 29 '18 at 04:02
  • 1
    To run the same query against multiple databases in SSMS, you could create a registered server group in SSMS containing the desired Azure databases. Then right-click on the group name and select new query to open a query window. Queries run in the multi-server query window with 1 or 2-part names will execute against each database and SSMS will combine the results (e.g. `select count(*) from dbo.foobar;`) See https://learn.microsoft.com/en-us/sql/ssms/register-servers/execute-statements-against-multiple-servers-simultaneously. – Dan Guzman Dec 29 '18 at 04:35

1 Answers1

1

Ones needs to create external data source and table that is linked to another server. You can use the following code to create one.

CREATE DATABASE SCOPED CREDENTIAL credname
WITH IDENTITY = 'username',
SECRET = 'password';

CREATE EXTERNAL DATA SOURCE data_source_name
WITH
(
 TYPE=RDBMS,
 LOCATION='server.database.windows.net',
 DATABASE_NAME='databasename',
 CREDENTIAL= credname
);

CREATE EXTERNAL TABLE [dbo].[external_table_name](
 -- Copy column definition
 [Id] [uniqueidentifier] NOT NULL,
 [Name] [nvarchar](200) NULL
)
WITH
(
DATA_SOURCE = data_source_name,
SCHEMA_NAME = 'dbo',
OBJECT_NAME = 'tablename'
)

select *
from dbo.[external_table_name]

DROP EXTERNAL TABLE [external_table_name]

DROP EXTERNAL DATA SOURCE [data_source_name]

DROP DATABASE SCOPED CREDENTIAL credname
Dibran
  • 1,435
  • 16
  • 24