Problem with Merge statement

• p.shabbir

  SSCrazy

  Points: 2404

  More actions

  January 4, 2013 at 2:39 pm

  #287282

  Hi All,

  I am trying to implement SCD2 using MERGE STATEMENT.

  The problem i am facing is its inserting new records but when comes to update,its updating old record and as well inserting as new record.

  For example

  I have a data in source like

  Eid Name Addr

  1 AAA Mysore

  2 BBB Bangalore

  3 ccc Chennai

  First time when we run three records get inserted

  Now if i add one new record and Change one record like

  New record -- 4 DDD Hyderabad

  Changed Record --1 AAA Mumbai

  So in my target it should be like below

  Eid Name Addr

  1 AAA Mysore

  2 BBB Bangalore

  3 ccc Chennai

  4 DDD Hyderabad

  1 AAA Mumbai

  But im my target the first record is updating as well it is inserting like

  1 AAA Mysore to changed 1 AAA Mumbai (This should not be changed)

  1 AAA Mumbai

  This is my Merge statement

  INSERT INTO Emp_Details_Trgt

  select eid,name,addr

  FROM

  (Merge Emp_Details_Trgt edt

  Using Emp_Details ed

  ON edt.eid = ed.eid

  WHEN MATCHED AND (edt.Addr <> ed.Addr) THEN

  UPDATE emp_Details_trgt set edt.name = ed.name,

  edt.addr = ed.addr

  OUTPUT $ACTION action_out,ed.eid,ed.name,ed.addr

  WHEN NOT MATCHED THEN

  INSERT VALUES(ed.Eid,ed.name,ed.addr);

  ) AS MERGE_OUT

  WHERE MERGE_OUT.ACTION_OUT = 'UPDATE';

  Where am i doing wrong please help me.

  Also if you have time can you please explain me about "OUTPUT $ACTION" and "MERGE_OUT.ACTION_OUT = 'UPDATE'"

  Thanks

• prashan

  SSC Journeyman

  Points: 78

  More actions

  January 4, 2013 at 3:03 pm

  #1573973

  eid column is primary jey, you cannot insert duplicates. use a different logic

  create table emp_targt

  (

  eid int ,

  name varchar(10),

  addr varchar(10))

  drop table emp_targt

  insert into emp_targt

  values(1,'aaa','Mysore')

  insert into emp_targt

  values(2,'bbb','chennai')

  create table emp_details

  (

  eid int ,

  name varchar(10),

  addr varchar(10))

  insert into emp_targt

  values(1,'aaa','mumbai')

  insert into emp_targt

  values(3,'bbb','chennai')

  insert into emp_targt select * from emp_details where not exists(select * from emp_targt)

  select * from emp_targt

  Regards,

  Prashanth Jayaram