August 18, 2013 at 3:32 am
hi friends i have small doubt in sql server plese tell me how to solve this issuse in sql server
i have 3 tables 1 for source table and 2nd for target table and 3rd for maintain deletes records table and
table name is cdc_source and id & sal are int datatypes and (name,dname,databasename )are varchar datatypes and
data like below
idnamesaldnamedatabasename
1veu15grdb1
2eu1jrdb2
3jd60vrdb2
4pd80trdb2
target table is cdc_target
idnamesaldnamedatabasename
maintain delete table is cdc_deletes
idnamesaldnamedatabasename
Here i implement query useing merge script in sql server to maintain delete records in other table(cdc_deletes) as well as to maintain
and source records changed (insert/updated/deleted)that would be effected target table(cdc_target).
first time i ran query that time what ever records in source table that records goes to target table.and next time i deleted
one record in source table .and again i run query that time what ever i deleted record in source that records also deleted
in target table.here i want maintain what ever i deleted records that records maintain to deleted tables.
i tried query like below
merge cdc_target.dbo.emp t
using cdc_source.dbo.emp s
on t.id=s.id and t.databasename=s.databasename
when matched then
update set t.name=s.name,
t.sal=s.sal ,
t.dname=s.dname
when not matched by target then
insert (id,name,sal,dname,databasename)values(s.id,s.name,s.sal,s.dname,s.databasename)
when not matched by target then
insert into cdc_source.dbo.cdc_deletes
select * from cdc_target.dbo.emp where id not in(
select id from cdc_source.dbo.emp);
when not matched by target then
--when not matched by source then
delete ;
but its not given exacetly.plese tell me how to solve this issuse useing merge script in sql server .
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply