Partager via

How to compare the rows of two tables and fetch the differential data.

  • Article

In this blog I would like to demonstrate a scenario where users want to move the changes between the tables in two different databases.

Let’s say we would like to compare and move the changes between the databases for some tables using T-SQL

The below example talks about moving the changes between the two databases in the same instance However the same can be extended across instances if you use linked server or SSIS packages.

Also we can write queries to move the DML changes from source to destination and vice versa. Let’s look at the below example

 

 

--creating a source database

create database source

 

--create source table

use source

 

create table Product(

 

Pid int primary key ,

Pname varchar (10),

Pcost float,

source int ,

location varchar(10))

 

--create destination database

 

create database Destination

 

--create destination table

 

use Destination

 

create table Product(

 

Pid int primary key ,

Pname varchar (10),

Pcost float,

source int,

location varchar(10) )

 

--Insert data into source table

use source

 

insert into product values ( 1,'rdbms',100,200,'ind')

insert into product values ( 2,'dbm',20,100,'US')

insert into product values ( 3,'arp',30,250,'UK')

insert into product values ( 4,'mqr',40,100,'ind')

insert into product values ( 5,'ttp',50,200,'us')

 

-- EXCEPT returns any distinct values from the left query that are not also found on the right query.

--The below query gives us difference between sourec and destination

-- we can use except ket word to look at selected columns or entire table

 

select * from source.dbo.product

 

except

 

select * from [Destination].dbo.product

 

--updating destination table with the changes from source

 

insert into [Destination].dbo.product

select * from source.dbo.product

except

select * from [Destination].dbo.product

 

-- We see that the destination is populated with all the rows from source

 

select * from [Destination].dbo.product

 

--Now lets update the row in the source and see how it works

 

update source.dbo.product

set pname='sql'

where pid =1

--run the below query

select * from source.dbo.product

 

except

 

select * from [Destination].dbo.product

 

-- the result gives us the only row which was changed in source

 

-- loading the deiffrences to a temp table

select * into #temp from source.dbo.product

 

except

 

select * from [Destination].dbo.product

 

--updating the destination with changes

 

update [Destination].dbo.product

set [Destination].dbo.product.pname= #temp.pname

from #temp where #temp.pid= [Destination].dbo.product.pid

 

--lets run the statement to see the difference between these tables

 

select * from source.dbo.product

 

except

 

select * from [Destination].dbo.product

 

--lets see how the delete works

 

delete from source.dbo.product where pid= 2

 

-- to see the rows which were deleted at source or inserted at destination only

select * from [Destination].dbo.product

except

select * from source.dbo.product

--based on the application logic either we will insert it back in the source or delete from dest

 

--lets say we want to delete from dest as well ,

 

select * into #temp from [Destination].dbo.product

except

select * from source.dbo.product

 

delete from [Destination].dbo.product where pid in ( select pid from #temp)

 

-- Now lets see that difference between the tables

select * from [Destination].dbo.product

except

select * from source.dbo.product

 

 

In the above demo, we see that how we can propagate the DML changes (insert, delete and update) From source to destination using T-SQL queries.

Also this can be used when you have requirement from your application to move only the changes Instead of truncating the entire table and populating the entire data again. 

Author:

Sravani Saluru, Support Engineer, Microsoft India GTSC

Reviewed by:

Karthick Krishnamurthy, Technical Advisor, Microsoft India GTSC

Comments

  • Anonymous
    November 23, 2013
    Nicely explained blog Sravani! Looking forward to some more:)

  • Anonymous
    April 18, 2014
    really very useful blog sravani..good and keep going...

  • Anonymous
    September 19, 2014
    Great blog, but can you tell me how to setup so that i can access tables from different databases! select * from [Server1].[dbo].[_Activity] except select * from [Server2].[dbo].[_Activity]

  • Anonymous
    June 07, 2015
    It's a great very very appropriate. Thanks to author..

  • Anonymous
    September 25, 2015
    How to compare and update destination table when we are not aware about which column and which row in source table got modified

  • Anonymous
    January 16, 2017
    except is deprecated