How To Transfer Data Between Databases In Azure SQL

Cross-Database Query in Azure SQL Database

Written by: Eric Rouach - Madeira Data Solutions - 2021

In this demo, we will demonstrate how to perform cross-database

queries in Azure SQL Server.

1) How to transfer data between two databases in Azure SQL?

2) Build a "One-Action-Transfer-Archive-Delete" process using the sp_execute_remote stored procedure.

For this demo, we have created a Microsoft Azure Subscription at https://portal.azure.com/.

Within this account, we have created two databases:

ProdDB

ArchiveDB

!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Important tip!!

Do not create a new database from SSMS: you would be automatically charged

according to Azure's default storage pricing tier while you may only need the Basic

2GB storage which is much cheaper.

Therefore, we recommend creating a new Azure SQL database from the Azure Portal.

!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

In our first scenario we will demonstrate how to transfer data between two databases in Azure SQL:

1) We have connected to our Azure SQL Server using SSMS.

We are connected as system admin.

2) In object explorer, we can see the two databases we previously created:

ProdDB

ArchiveDB

3) Let's create a table in our ProdDB: (Make sure you are in the right DB)

*/

IF OBJECT_ID('dbo.ProdTable', 'U') IS NOT NULL

DROP TABLE dbo.ProdTable;

CREATE TABLE dbo.ProdTable

(

Id INT PRIMARY KEY,

Name VARCHAR(20) NOT NULL,

LastName VARCHAR(30) NOT NULL,

Cell VARCHAR(12) NOT NULL,

Email VARCHAR(60) NOT NULL,

UserId INT

);

GO

--Let's insert some records in our ProdTable

INSERT INTO ProdTable

(

Id,

Name,

LastName,

Cell,

Email,

UserId

)

VALUES

(1,'Vlad', 'Borvski', '91551234567', 'email3@contoso.com', 1),

(2,'Juan', 'Galvin', '95551234568', 'email2@contoso.com', 2),

(3,'Julio', 'Calderon', '95551234569', 'email1@contoso.net',3),

(4,'Fernando', 'Cobo', '86168999', 'email0@email.com', 4);

--Let's check the dbo.ProdTable content:

SELECT

Id,

Name,

LastName,

Cell,

Email,

UserId

FROM

dbo.ProdTable

4) Let's suppose we have been requested to archive some of the records from the

dbo.ProdTable table to the ArchiveDB for organization goals;

Our primary intuition will be to create a table in

ArchiveDB to store the archived records:

First, switch to the ArchiveDB using SSMS; the "USE" statement is not supported in

Azure SQL to switch between databases!

(You will have to switch to the master database first)

IF OBJECT_ID('dbo.ArchiveTable', 'U') IS NOT NULL

DROP TABLE dbo.ArchiveTable;

CREATE TABLE dbo.ArchiveTable

(

Id INT PRIMARY KEY ,

Name VARCHAR(20) NOT NULL,

LastName VARCHAR(30) NOT NULL,

Cell VARCHAR(12) NOT NULL,

Email VARCHAR(60) NOT NULL,

UserId INT

);

SELECT

Id,

Name,

LastName,

Cell,

Email,

UserId

FROM

[dbo].[ArchiveTable]

5) Now let's insert a record we wish to archive into the newly created ArchiveTable:

Our primary intuition is to run an "INSERT INTO SELECT" command as following:

INSERT INTO [ArchiveDB].[dbo].[ArchiveTable]

SELECT

Id,

Name,

LastName,

Cell,

Email,

UserId

FROM

[ProdDB].[dbo].[ProdTable]

WHERE

[Id] = 4

This command fails! We get the following error:

Reference to database and/or server name in 'ArchiveDB.dbo.ArchiveTable'

is not supported in this version of SQL Server.

The databases can't physically "see" each other in Azure SQL Server.

Therefore, the process for transferring data from one database to another in this case

is not the same as if our SQL Server was On-Premises.

We need to create the following objects in our ArchiveDB:

-master key

-database scoped credential

-external data source

-external table

6) In ArchiveDB, let's create a Master Key:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Strongpassword123'

--Let's check our Master Key has been created:

SELECT * FROM sys.symmetric_keys

7) Still in ArchiveDB, let's create a DATABASE SCOPED CREDENTIAL:

CREATE DATABASE SCOPED CREDENTIAL DBScopedCredential

WITH

IDENTITY = 'yourlogin', --the IDENTITY is the Login Name we used to login to the Server.

SECRET = 'yourpassword'; --the SECRET is the Password we used to login to the Server.

8) Now, still in ArchiveDB, let's create an External Data Source to determine which

database is going to be our data source:

CREATE EXTERNAL DATA SOURCE ProdDatabase

WITH

(

TYPE = RDBMS,--Relational Database Management System

LOCATION = 'yourserver.database.windows.net', -- Our server name.

DATABASE_NAME = 'ProdDB', -- The database we use as our data source.

CREDENTIAL = DBScopedCredential -- the name we gave to our DATABASE SCOPED CREDENTIAL.

);

9) Let's create an External Table in ArchiveDB; the external table will be used as a

"temporary copy" of the [dbo].[ProdTable] from the ProdDB database:

CREATE EXTERNAL TABLE dbo.ProdTable_Ext

(

Id INT ,

Name VARCHAR(20) NOT NULL,

LastName VARCHAR(30) NOT NULL,

Cell VARCHAR(12) NOT NULL,

Email VARCHAR(60) NOT NULL,

UserId INT

)

WITH

(

DATA_SOURCE = ProdDatabase,

SCHEMA_NAME = 'dbo',

OBJECT_NAME = 'ProdTable'

);

--Let's check the external table dbo.Copy_Of_ProdTable_Ext has been created

SELECT

Id,

Name,

LastName,

Cell,

Email,

UserId

FROM

dbo.ProdTable_Ext

10) Now we can easilly insert data into the [dbo].[ArchiveTable]:

INSERT INTO [dbo].[ArchiveTable]

SELECT

Id,

Name,

LastName,

Cell,

Email,

UserId

FROM

[dbo].[ProdTable_Ext]

WHERE

Id = 4

--Let's check if the specified row has been inserted into [dbo].[ArchiveTable]

SELECT

Id,

Name,

LastName,

Cell,

Email,

UserId

FROM

[dbo].[ArchiveTable]

If necessary, we can use a simple DELETE statement to delete the archived row from

the [dbo].[ProdTable] table.

=====================================================================

Now, we will Build an "Archive & Delete" process using the sp_execute_remote stored procedure

without using an external table.

This very useful object will allow us to remotely execute a stored procedure

in one database from another database.

We still need the previously created objects:

-master key

-database scoped credential

-external data source

Let's have a demo to show this process:

Let's switch to the ProdDB

1) Let's check for the existing records in the [dbo].[ProdTable]

SELECT

Id,

Name,

LastName,

Cell,

Email,

UserId

FROM

[dbo].[ProdTable]

GO

2) In the [ProdDB] database, let's create a stored procedure for selecting

a record from the [dbo].[ProdTable] table:

CREATE OR ALTER PROCEDURE usp_select_from_ProdTable

@Id INT

AS

BEGIN

SELECT

Id,

Name,

LastName,

Cell,

Email,

UserId

FROM

[dbo].[ProdTable]

WHERE

Id = @Id

END

GO

And another stored procedure for deleting this same record from

the [dbo].[ProdTable] table:

CREATE OR ALTER PROCEDURE usp_delete_from_ProdTable

@Id INT

AS

BEGIN

DELETE [dbo].[ProdTable]

WHERE Id = @Id

END

GO

3) Now, in the [ArchiveDB] database, we will create a procedure to

import data from the [dbo].[ProdTable] table, insert this data into our [dbo].[ArchiveTable]

table, and finally delete the data from the ProdTable:

CREATE OR ALTER PROCEDURE usp_archive_record_from_prod

AS

BEGIN

--Let's create a temporary table for holding the record/s from [dbo].[ProdTable] table:

DROP TABLE IF EXISTS #TempTable

CREATE TABLE #TempTable

(

Id INT PRIMARY KEY ,

Name VARCHAR(20) NOT NULL,

LastName VARCHAR(30) NOT NULL,

Cell VARCHAR(12) NOT NULL,

Email VARCHAR(60) NOT NULL,

UserId INT

)

--Let's insert the result of sp_execute_remote into #TempTable

INSERT INTO #TempTable

EXEC sp_execute_remote

@data_source_name = N'ProdDatabase', --name of the external data source

@stmt = N'usp_select_from_ProdTable @Id', --name of the stored procedure

@params = N'@Id INT', --specify the parameter(s)

@Id = 4 --specify the value for each parameter

END

GO

EXEC usp_archive_record_from_prod

GO

--This will result in the following error:

Column name or number of supplied values does not match table definition.

??????????????????????????????????????????????????????????????????????????????

This is because when sp_execute_remote executes a select statement,

its output will automatically display an auto-created additionnal column called

"ShardName" of VARCHAR(128) datatype. (It is important not to miss that in Microsoft Documentation!)

Therefore, when creating the temporary table, this additionnal column

must be taken in consideration!

Let's re-write the stored procedure creating the temporary table the right way:

CREATE OR ALTER PROCEDURE usp_archive_record_from_prod

AS

BEGIN

--Let's create a temporary table for holding the record/s from [dbo].[ProdTable] table:

DROP TABLE IF EXISTS #TempTable

CREATE TABLE #TempTable

(

Id INT PRIMARY KEY ,

Name VARCHAR(20) NOT NULL,

LastName VARCHAR(30) NOT NULL,

Cell VARCHAR(12) NOT NULL,

Email VARCHAR(60) NOT NULL,

UserId INT ,

ShardName VARCHAR(128) -- <===========

)

--Let's insert the result of sp_execute_remote into #TempTable

INSERT INTO #TempTable

EXEC sp_execute_remote

@data_source_name = N'ProdDatabase', --name of the external data source

@stmt = N'usp_select_from_ProdTable @Id', --name of the stored procedure

@params = N'@Id INT', --specify the parameter / list of parameters as a string

@Id = 4 --specify the value for each parameter

SELECT

Id ,

Name ,

LastName ,

Cell ,

Email ,

UserId ,

ShardName

FROM

#TempTable

END

GO

EXEC usp_archive_record_from_prod

GO

--Let's re-write the stored procedure with the full process of import-archive-delete:

CREATE OR ALTER PROCEDURE usp_archive_record_from_prod

AS

BEGIN

--Let's create a temporary table for holding the record/s from [dbo].[ProdTable] table:

DROP TABLE IF EXISTS #TempTable

CREATE TABLE #TempTable

(

Id INT PRIMARY KEY ,

Name VARCHAR(20) NOT NULL,

LastName VARCHAR(30) NOT NULL,

Cell VARCHAR(12) NOT NULL,

Email VARCHAR(60) NOT NULL,

UserId INT ,

ShardName VARCHAR(128) -- <===========

)

--Let's insert the result of sp_execute_remote into #TempTable

INSERT INTO #TempTable

EXEC sp_execute_remote

@data_source_name = N'ProdDatabase', --name of the external data source

@stmt = N'usp_select_from_ProdTable @Id' , --name of the stored procedure

@params = N'@Id INT', --specify the parameter(s)

@Id = 4 --specify the value for each parameter

--Let's use a MERGE statement to populate the [dbo].[ArchiveTable] with the

--result set held by #TempTable

MERGE

[dbo].[ArchiveTable] AS arc

USING

#TempTable AS tmp

ON

arc.[Id] = tmp.[Id]

--Insert records that don't exist into the Archive table

WHEN NOT MATCHED BY TARGET

THEN INSERT

(

Id,

Name,

LastName,

Cell,

Email,

UserId

)

VALUES

(

tmp.[Id],

tmp.[Name],

tmp.[LastName],

tmp.[Cell],

tmp.[Email],

tmp.[UserId]

);

PRINT CAST(@@ROWCOUNT AS VARCHAR) + ' rows were merged to archive table'

--Let's drop the #TempTable

DROP TABLE #TempTable

--Finally, let's delete the record we archived from the [dbo].[ProdTable] table:

EXEC sp_execute_remote

@data_source_name = N'ProdDatabase', --name of the external data source

@stmt = N'usp_delete_from_ProdTable @Id', --name of the stored procedure

@params = N'@Id INT', --specify the parameter(s)

@Id = 4 --specify the value for each parameter

END

GO

EXEC usp_archive_record_from_prod

--We can see that 0 rows were merged into the [dbo].[ArchiveTable] table;

--that is because the row we wanted to archive was already there.

4) Let's check if our record has well been imported into [dbo].[ArchiveTable]

SELECT

Id,

Name,

LastName,

Cell,

Email,

UserId

FROM

[dbo].[ArchiveTable]

And well deleted from [dbo].[ProdTable]

SELECT

Id,

Name,

LastName,

Cell,

Email,

UserId

FROM

[dbo].[ProdTable]

--=============THE END=============

--Cleanup Tasks:

--In ProdDB

--drop proc [dbo].[usp_delete_from_ProdTable]

--drop proc [dbo].[usp_select_from_ProdTable]

--drop table [dbo].[ProdTable]

--In ArchiveDB

--drop table [dbo].[ArchiveTable]

--drop external table [dbo].[ProdTable_Ext]

--drop proc [dbo].[usp_archive_record_from_prod]

--drop external data source ProdDatabase

--drop database scoped credential DBScopedCredential

--drop master key