Help with SQL Query

• Ash-N

  Say Hey Kid

  Points: 703

  More actions

  September 7, 2006 at 11:09 am

  #119448

  I have two different source tables from which i need to get the data and put them in a destination table

  The two source table are SRC1 and SRC2 and the DEST Table is Destination

  SRC1 - SRC1_ID

    SRC1_NAME

    SRC2_ID

    LAST_UPD_DT

  SRC2 -  SRC2_ID

    SRC2_NAME

    SRC1_ID

    LAST_UPD_DT

  DEST  - DEST_ID

    SRC1_ID

    SRC2_ID

    LAST_UPD_DT

  Need to check for records in SRC1 where SRC.LAST_UP_DT > DEST.LAST_UP_DT

  If there are brand new records

  i.e SRC1.SRC_ID NOT FOUND in DEST.SRC_ID AND

      SRC2.SRC_ID NOT FOUND in DEST.SRC_ID THEN

   INSERT SRC1 record in to DEST table

  If the record is an existing record

  i.e SRC1.SRC_ID is found in DEST.SRC_ID OR

   SRC2.SRC_ID is foind in DEST.SRC_ID THEN

   UPDATE SRC2_NAME, SRC1_NAME, LAST_UP_DATE in DEST table.

  Do this for all the new records found in the Source table

  Could anybody help me with the SQL Query please

  Thanks

  A-

• Sreejith Sreedharan

  SSCertifiable

  Points: 5403

  More actions

  September 7, 2006 at 11:34 am

  #659174

  Here you. This should get you close.

   

  --Update Existing

  Update Destination set  SRC1_NAME = SOURCE.SRC1_NAME,

     SRC2_NAME = SOURCE.SRC2_NAME,

     LAST_UP_DATE = SOURCE.LAST_UP_DATE

  from (Select SRC1.SRC1_ID,SRC2.SRC2_ID,SRC1.SRC1_NAME, SRC2.SRC2_NAME, SRC1.LAST_UP_DATE

   --SRC1.SRC1_NAME,SRC1.SRC2_ID,SRC1.LAST_UPD_DT

   from SRC1 INNER JOIN SRC2 ON SRC1.SRC1_ID= SRC1.SRC1_ID ) SOURCE

  where Destination.SRC1_ID = SOURCE.SRC1_ID and Destination.SRC2_ID = SOURCE.SRC2_ID

   and (

     isnull(Destination.SRC1_NAME,'') <> Isnull(SOURCE.SRC1_NAME,'') OR

     isnull(Destination.SRC2_NAME,'') <> Isnull(SOURCE.SRC2_NAME,'') OR

     isnull(Destination.LAST_UP_DATE,'1/1/1900') <> Isnull(SOURCE.LAST_UP_DATE,'1/1/1900'))

   &nbsp

  --Insert New

  Insert into Destination(SRC1_ID,SRC2_ID,SRC1_NAME,SRC2_NAME,LAST_UP_DATE)

  Select SRC1.SRC1_ID,SRC2.SRC2_ID,SRC1.SRC1_NAME, SRC2.SRC2_NAME, SRC1.LAST_UP_DATE

   --SRC1.SRC1_NAME,SRC1.SRC2_ID,SRC1.LAST_UPD_DT

  from SRC1 INNER JOIN SRC2 ON SRC1.SRC1_ID= SRC1.SRC1_ID

  where not exists (Select 1 from Destination where Destination.SRC1_ID = SRC1.SRC1_ID

      and Destination.SRC2_ID = SRC1.SRC2_ID

  Thanks

  Sreejith