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
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