merge script issuse in sql server .

  • 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