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;