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"

Comments

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
Replies

Add comment

Most Loved Posts

Threadpool - A deadly poison wait for SQL Server (The What, When and How)

Introduction Threadpool is a poison wait. Yes, I mean it. Its poison for SQL Server, its poison for the Business and of course, the end-users! The most devastating thing about threadpool is you hardly recognize it because it comes in disguise, meaning you see no memory or cpu pressure in the system, yet you cannot run any query, it seems like your SQL Server is frozen solid. That scary, isn't it?

SQL Schema Compare with Visual Studio (A complete Guide)

Introduction When you're working on your Dev Database, an urgent issue comes along, and you instantly solve it by changing Scheme in the Staging Database or Production Database :3, few more these type of patching and you're completely out of sync! A lot of paid alternatives are there like SQL Data Compare by RedGate, but my first choice is Visual Studio's SQL Data Tools. In the following article, I tried to image-describe the steps for SQL Data Tool. Like I said before, there are lots of handly DBAtools out there to compare Schema between two DB Sources. I would like to discuss how you can compare two SQL Server DB with Visual Studio. Make sure you have SQL Server Data tools checked while installing Visual Studio.

SQL Insider 01 : An Anatomy of SELECT

Introduction When we write queries, we tend to think about the internals very little. In the new series of SQL Insider, I shall try to demonstrate what your SQL Server has to go through when you write a specific query, more specifically a specific operator. In the series, we shall try to cover all the important operators in SQL. Our today's SQL participant in SELECT. SELECT With the SELECT query, we can select one, some, or all the columns of a SQL table. The typical syntax for SELECT is like this SELECT * FROM Sales.SalesOrderDetail SELECT sod.OrderQty, sod.UnitPrice FROM Sales.SalesOrderDetail sod Please note that we will not be dealing with WHERE clause in today's episode. Database We will be using AdventureWorks2019 Database for the demonstration Important Configuration We will be setting STATISTICS IO ON like this - SET STATISTICS IO ON; SET STATISTICS IO ON ; We will turn on Actual Execution Plan to examine the query SQL Insider Let's sta...

How to Backup SQL server like Batman: The Ultimate SQL DBA Guide

Slow SQL Server : What we should NOT do

Try to list the best practices of SQL Server. It will require a heck of a time. Try to list the Bad Practices and it will require more than the best practice list , of course, probably you’ll end up getting frustrated . (seeing all the oops configurations and its effect on SQL Server )

How to deal with Slow SQL Server due to Autogrowth issue

Why you should not stick to SQL Server’s default Initial file size and autogrowth We hear a lot of these statements : My SQL Server is running slow My Production DB was fine when we started, But it is staggeringly slow now My Business end users are frustrated to wait too long Well, there are lots of reasons why your SQL Server might be slow. Setting the Autogrowth option to default is definitely one of the vital ones which we seem to ignore most of the time. Slow SQL Server and Tortoise SQL Server provides you with some default settings for autogrowth when you install it for the first time. These default cases are defined with increment by 8MB or by 10%. You need to change it to suit your own needs. For Small application, this default value might work but as soon as your system grows, you feel the impact of it more often. What Happens SQL Server Files needs more space SQL Server Requests the Server PC for more space The Server PC takes the request and asks the SQL request...

How to configure your Availability Group listener to ASP.NET

SQL Server’s availability group Always On feature is great to have features for your Database. Anytime one of your database nodes goes down, your secondary replica will automatically take over. After a failover, your secondary cluster node becomes the primary cluster. Now the question arises, “Do I need to configure my APP server connectionstring each time I face a failover cluster?”. The answer is NO, you don’t have to configure your app server connectionstring every time. Default ConnectionString By default, your App server connectionstring looks something like this – <connectionStrings> <add name="ConnStringDb1" connectionString="Data Source=localhost;Initial Catalog=YourDataBaseName;Integrated Security=True;" providerName="System.Data.SqlClient" /> </connectionStrings> ConnectionString for Failover Partner You can manually specify the failover partner in your connectionstring like this <connectionStrings> <a...

Intelligent Query Processing in SQL Server 2019 Big Data

SQL Server 2019: Intelligent Query Processing SQL Server 2019 ships with some brand-new features. Many of these features are targeted for Big Data Solutions. No wonder in that, since the world is moving faster towards Big Data and it is absolutely necessary to cope up with that. Today we will discuss one such feature called Approximate Query Processing. Approximate Query Processing SQL Server ships with Intelligent Query Processing out of the box with SQL Server 2019 installation. Approximate Query processing is a part of Intelligent Query Processing. Things we will be covering in this article – Understand the need for Approximation with Case Study Case Study 1: Railway Case Study 2: e-commerce How to use Approximate Query Processing Demo Code for Comparing Performance Results Limitations When to avoid Approximate Query Processing Understand the need for Approximate Query Processing Before using any technological feature, we must understand why we should use it? Should we jus...

Maxdop and Cost theshold for parallelism SQL Server

Maxdop Maxdop stands for max degree for parallelism. Let's say, the maxdop is set to 4, it means during parallel plan execution, SQL server is going to use 4 processors. If you set your Maxdop settings to 0, it means SQL server is going to use as many processors it needs to complete your request.

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>(); ...

SQL Server Pain Relief

Search This Blog