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

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