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
create table Product(
Pid int primary key ,
Pname varchar (10),
source int ,
--create destination database
create database Destination
--create destination table
location varchar(10) )
--Insert data into source table
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
select * from [Destination].dbo.product
--updating destination table with the changes from source
insert into [Destination].dbo.product
-- We see that the destination is populated with all the rows from source
--Now lets update the row in the source and see how it works
where pid =1
--run the below query
-- 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
--updating the destination with changes
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
--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
--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
delete from [Destination].dbo.product where pid in ( select pid from #temp)
-- Now lets see that difference between the tables
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.
Sravani Saluru, Support Engineer, Microsoft India GTSC
Karthick Krishnamurthy, Technical Advisor, Microsoft India GTSC
Nicely explained blog Sravani! Looking forward to some more:)
really very useful blog sravani..good and keep going...
Great blog, but can you tell me how to setup so that i can access tables from different databases!
select * from [Server1].[dbo].[_Activity]
select * from [Server2].[dbo].[_Activity]
It's a great very very appropriate. Thanks to author..