Copy data between Azure Databases

As a Data Scientist, I’m always looking for ways to combine data in a smart and efficient way. Ever since I’ve started working with SQL databases (which is since forever, or so it feels), I’ve been taking advantage of the fact that you can easily reach two different databases in the same query as long as they are on the same server (or on a linked server of course). You can imagine my surprise when trying this on two Azure databases on the same server… Only errors came my way. Each Azure database might be on the same Azure server from my point of view, but since this is a virtual server, Azure does not view them as being on the same server. So my regular way of working didn’t do the trick here for me. After an online search I’ve found some hints on how to quickly address this problem. But since it still took me some time to figure out the complete solution, I wanted to save others (you!) this time and show the complete solution with some simple explanation. As always, it’s easy as soon as you know how to do it.

We start by opening our SQL Server Management Studio and connecting to the Azure database to which we want to copy or use data from another database in (our target database). All code below will be executed in this target database. If we talk about a source database, we mean the database where the data lives which we want to copy or use in our target database.

In the script below we assume you want to copy data from one database to another, but you could of course also keep the connection and reference table(s) and use them like that in your queries from now on. This can be especially useful if you have a dataset that you need in multiple databases and that changes regularly.

I hope I saved you some time by providing you with this short and useful SQL code, enjoy!