In the previous post we saw that we could migrate data from a local SQL Server database to Azure by generating a data insertion script. This was very simple because there was no information in the target database.

However, what happens when there is already data in the Azure database? Well, we need to migrate the data taking this into account so as not to duplicate records or generate errors.

Visual Studio to the Rescue

A very direct way to compare data in different tables is by using a Visual Studio tool that helps us to compare both data and schemas, and allows us to generate scripts that correct any discrepancies.

Specifically, for our example, we have the following situation:

We have a table in SQL Server (our source), which has data. Also, we have a table in Azure SQL Database (our target) which has data. However, as you can see, the source has a record that the target does not, and this is the record with Id 4 called “Fantasia”. How can we generate a script that corrects this discrepancy?

Well, for that we can use Visual Studio:

1. Open Visual Studio
2. Go to Tools> SQL Server> New Data Comparison…
3. You should get this dialog box:

As you can see, on the left we can select a source database, and on the right, we can select a target database.

4. Click the left button Select Connection…
5. Hit Browse
6. Find the instance of SQL Server where your database is located. In my case it is a local instance, therefore I will select Local> My instance:

7. If necessary, enter your credentials, select the database under Database Name, and press Connect.

With this we connected to the source DB, now, we go to the target DB in Azure.

8. Click the Select Connection… button on the right
9. Click Browse
10. Expand Azure

10.1. Connect with the Azure account which has access to the target database.

11. Select the destination database, enter the credentials and press Connect.

With this we are already connected with the source and destination databases.

12. In the data comparison options, if you want, you can leave them all just to see what it throws at us, but the option we need is the one that says “Only in Source”.
13. Press Next. (This part may take a minute or two)
14. Here you can select the tables to compare:

In my case, I’m just going to select the Generos (Genres) table.

15. Select the desired tables, and press Finish.

The result is that I have a record in the source DB that is not in the target DB. I also get that there are 6 identical records:

You have the option of generating a script to correct the discrepancies (with the Generate Script… button at the top), or make a direct update (with the Update Target button also at the top). I personally like to generate the script to visualize what is going to happen and run it myself.

16. We click on Generate Script…
17. We can copy the generated script and run it in our Azure database to insert the missing data.

And that’s it, with this we have migrated data from local SQL Server to Azure respecting the data already existing in the target database.

Courses

If you want to learn more, buy one of my Udemy courses today:

1. Building Applications with React 17 and ASP.NET Core 6: https://www.udemy.com/course/building-applications-with-react-and-aspnet-core/?couponCode=SEPTEMBER2021
2. Building Applications with Angular 11 and ASP.NET Core 5: https://www.udemy.com/course/building-applications-with-angular-and-aspnet-core/?couponCode=SEPTEMBER2021
3. Programming in Blazor – ASP.NET Core 5: https://www.udemy.com/course/programming-in-blazor-aspnet-core/?couponCode=SEPTEMBER2021
4. Building RESTful Web APIs with ASP.NET Core 3.1: https://www.udemy.com/course/building-restful-web-apis-with-aspnet-core/?couponCode=SEPTEMBER2021
5. Introduction to Concurrency in C# – Async and Paralellism: https://www.udemy.com/course/introduction-to-concurrency-in-c-async-and-paralellism/?couponCode=SEPTEMBER2021

Kind regards!