Eric RouachHow 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;