CREATE
MASTER
KEY
ENCRYPTION
BY
PASSWORD
=
'p@$$w0rd'
;
DATABASE
SCOPED CREDENTIAL db_analyst
WITH
IDENTITY =
'db_analyst'
,
SECRET =
Now we can
create
the External Data Source, that will use the previous credential
to
access a specific
database
in
a remote Azure SQL
Database
server:
EXTERNAL DATA SOURCE RemoteData
(
TYPE=RDBMS,
LOCATION=
'remoteserver.database.windows.net'
DATABASE_NAME=
'RemoteDB'
CREDENTIAL= db_analyst
);
Finally we can use the external Data Source
define a new External
Table
as
follows:
EXTERNAL
TABLE
[dbo].[country](
[country_code]
int
NOT
NULL
[country_name] nvarchar(256)
[country_continent] nvarchar(20)
)
DATA_SOURCE = RemoteData
And
now the new external
table
can be queried
it were a
local
or
view
and
eventually be joined / inserted
into
tables:
SELECT
*
FROM
[dbo].[country]
CREATE EXTERNAL TABLE [dbo].[remote_country](
[country_code] int NOT NULL,
[country_name] nvarchar(256) NULL,
[country_continent] nvarchar(20) NULL
,SCHEMA_NAME = 'dbo'
,OBJECT_NAME = 'country'