Warning: Undefined array key "HTTP_REFERER" in /www/wwwroot/prod/www.enjoyasp.net/wp-content/plugins/google-highlight/google-hilite.php on line 58

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.