-- For Azure:
CREATE MASTER KEY; -- create master key
GO
CREATE DATABASE SCOPED CREDENTIAL CrossDbCred
WITH IDENTITY = 'CrossDb', -- login user name
SECRET = 'P@ssword'; -- password
GO
CREATE EXTERNAL DATA SOURCE table2
WITH
(
TYPE=RDBMS,
LOCATION='server.database.windows.net',
DATABASE_NAME='database2',
CREDENTIAL=CrossDbCred
);
GO
try also another LOCATION format:
LOCATION = 'sqlserver://WINSQL2019'
See here: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-external-data-source-transact-sql?view=sql-server-ver15&tabs=dedicated
-- in database1
CREATE EXTERNAL TABLE [dbo].[table2]
( -- in database1.table2 the same columns as in database2.table2
colimn1 INT,
...
)
WITH (DATA_SOURCE = [database2],
SCHEMA_NAME = 'dbo',
OBJECT_NAME = 'table2'
);
GO