Azure: Copy data from one database to another using Azure Data Factory – I

In this post, we will talk about how to copy data of a table from one database to another using Azure.

Here we are using the following Azure resources:

1. Azure SQL Server
2. Azure SQL Database
3. Azure Data Factory

To begin, let’s login to Azure portal. After successful login let’s go through the below steps to see it in action.

1. Create Azure SQL Server:

Click on Create a resource in Azure portal –> Search for SQL Server(logical..

On the next screen, Click Create

Fill the mandatory fields and click on Create.

2. Create Azure SQL Database:

After creating the Azure SQL Server, let’s create 2 Azure SQL databases:

i) SourceSQLDB and ii) DestinationSQLDB

Click on Create a resource –> Select Databases –> SQL Database

To create Source Database, fill the mandatory fields and click on Create. Select Server as adf(the one we have created in the first step). For demo purpose, we have selected Source as Sample(AdventureWorksLT) to have some sample data as shown below:

Similarly let’s create Destination Database. Select Server as adf(the one we have created in the first step).

Once we are done with creating SQL server and SQL databases, let’s create a view in the source DB from the sample tables/data([SalesLT].[Customer]) we have in the source DB.

create view [dbo].[CustomersView]
as
SELECT [CustomerID]
,[Title]
,[FirstName]
,[MiddleName]
,[LastName]
,[Suffix]
,[CompanyName]
FROM [SalesLT].[Customer]

In this demo, we’ll copy the data from the above view to a table in the destination DB. Let’s create a table in destination DB to which data will be copied.

CREATE TABLE [dbo].[CustomersView](
[CustomerID] [int] IDENTITY(1,1) NOT NULL,
[Title] [nvarchar](8) NULL,
[FirstName] [varchar](100) NOT NULL,
[MiddleName] [varchar](100) NULL,
[LastName] [varchar](100) NOT NULL,
[Suffix] [nvarchar](10) NULL,
[CompanyName] [nvarchar](128) NULL
)

Below is the summary of what we have done so far:

Once, we are done with creating view in source DB and table in destination DB for copying data, let’s proceed with creating Azure Data Factory which we will discuss in the next post.