Querying across databases with Azure SQL

Question

I'm trying to insert data from one database table into another database table (on the same server) in Azure SQL. I see the following:

https://azure.microsoft.com/en-us/blog/querying-remote-databases-in-azure-sql-db/

This describes that I should be able to do the following:

CREATE EXTERNAL DATA SOURCE RemoteReferenceData
WITH
(
    TYPE=RDBMS,
    LOCATION='myserver.database.windows.net',
    DATABASE_NAME='_2016-09-07-17412',
    CREDENTIAL= SqlUser
);

CREATE EXTERNAL TABLE [dbo].[RemotePhotos](
    [PhotoId] int NOT NULL,
    [Url] nvarchar(max) NULL,
)
WITH
(
    DATA_SOURCE = RemoteReferenceData
);

However I get the following when I run this:

The specified credential cannot be found.

How can I go about creating a credential for this purpose? Is there a better way to go about this?

Answer 1

This is what I ended up with:

-- Cleanup
DROP EXTERNAL TABLE OldPhoto
DROP EXTERNAL DATA SOURCE RemoteReferenceData
DROP DATABASE SCOPED CREDENTIAL credentialName
DROP MASTER KEY
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';

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

CREATE EXTERNAL DATA SOURCE RemoteReferenceData
WITH
(
    TYPE=RDBMS,
    LOCATION='myserver.database.windows.net',
    DATABASE_NAME='mydb',
    CREDENTIAL= credentialName
);

CREATE EXTERNAL TABLE dbo.OldPhoto(
    [PhotoId] [int] NOT NULL,
    [Url] [nvarchar](300) NULL
)
WITH
(
    DATA_SOURCE = RemoteReferenceData
);

Select top 20 * from dbo.OldPhoto

The challenging bits were:

• I was pulling data from a backup with a table that had the same name. I had to rename the source table.
• The table creation has to match the schema of the current table in the external source.