Skip to main content

SQL Data Tools - Compare Data

Compare Data between two tables

SQL Server Database with the same schema architecture can differ in different environments like Dev, Staging, and Production, especially in configuration tables. Let's see how we can easily sync the data in two different tables.

                                  Figure: SQL Server Data Tools can be handier than any of these tools

Visual Studio SQL Data Tools

Tool -> SQL  Server -> New Data Compare

SQL Server Data Compare

Then we have to select source and destination. Then check the options you want to compare

SQL Data Compare

Then select the objects you want to compare (table or view)
After comparison, you can output something like this - 

SQL different records


  • Different Records - Records that exist in both source and destination. These will be updated in destination
  • Only In Source - Data in Source DB, NOT in Destination, these data need to be added
  • Only in Destination - Data in Destination DB, NOT in source, these data need to be deleted
  • Identical Records - Data Identical in both source and destination.

Click on generate scripts, you will get a script for this data migration :D 

data migration with script

You can run it in Visual Studio Or SSMS

SSMS update data SQL Data Tools

Run this script on destination to make it the same as the source

This script performs its actions in the following order:

  • Disable foreign-key constraints.
  •  Perform DELETE commands. 
  • Perform UPDATE commands.
  • Perform INSERT commands.
  • Re-enable foreign-key constraints.

Please back up your target database before running this script. Because you know, murphy's law states that, "If anything can go wrong, it will" 

Labels:
Location: Dhaka, Bangladesh

Comments

  1. Collabloud LimitedOctober 30, 2020 at 9:36 PM

    Excellent Post. The post is very useful to me as it contains some unique information about SQL which is really amazing. Thanks for posting it. Looking for Data Replication Software Online then visit on Repstance.

    ReplyDelete

Post a Comment

Most Loved Posts

How to encrypt an entire excel file using AES-128 encryption

What Is AES? AES stands for Advanced Encryption Standard. It is a symmetric block cipher that is used by the U.S. government to protect classified information. AES is used worldwide to protect classified data around the world. AES is essential in cybersecurity, electronic data protection, and computer security. Variations of AES AES is used in three block cipher versions namely AES 128, AES 192, AES 256. AES 128 uses 128-bit key length to encrypt and decrypt block messages. This is a symmetric secret key which means it uses same secret key for encrypting and decrypting message blocks. AES Encryption of Excel File Today we will AES 128 to encrypt an Excel File. The excel file looks like the following image. To encrypt this excel file, we will use C#. First, we need to create a console project. Let's name the project ExcelEncryption. First, we try to understand what we need to achieve. We need a KEY and Initialization Vector (IV) pair for AES 128 encryption. Let’s say, th...
Post a Comment
Read more

How to generate C# Class from SQL Server Table

C# Class from SQL Database Table There are multiple ways you can generate a C# class from your Database Table. We will be covering the following topics in today’s article. Generate Class with foreign key relation Generate Class with only entities Generate Class with foreign key relations For this we can simply use Entity Frameworks EDMX update feature which will generate our C# class from Database Tables. The output from EDMX will contain foreign key relations which we can see from the virtual interfaces like this -  public partial class AssetItem { [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")] public AssetItem() { this .AssetItemDepreciations = new HashSet<AssetItemDepreciation>(); this .AssetTaxMappings = new HashSet<AssetTaxMapping>(); this .AssetVatMappings = new HashSet<AssetVatMapping>(); ...
Post a Comment
Read more