update/append query help needed ...

• ralphnoble

  Newbie

  Points: 3

  More actions

  October 28, 2002 at 4:00 pm

  #80047

  First, thanks for reading ... As to my needs, I obtain a flat text file each month that includes both new records and updated records. Each row has a unique ID called ID. I have created a DTS package that imports the flat text file and places it in a temporary table called NEWDATA. I then need to ...

  1. UPDATE QUERY: Link the two databases (where NEWDATA.ID = OLDDATA.ID) and update the existing fields that changed.

  2. APPEND QUERY: Link the two database (where NEWDATA.ID <> OLDDATA.ID) and append the new records from NEWDATA.

  Again, only two types of records can appear in the flat text file: The ID is either a new record or is an update to an existing record and thus all fields must be updated in the OLDDATA table.

  Any suggestions on how I can accomplish this?

  Thanks,

  Ralph Noble

  ralph_noble@hotmail.com

• Ian Scarlett

  SSC-Insane

  Points: 23197

  More actions

  October 29, 2002 at 6:10 am

  #439685

  Your update query needs to join the old and new tables similar to this

  UPDATE OLDDATA SET olddata.col1 = newdata.col1,....

  FROM NEWDATA

  INNER JOIN OLDDATA on NEWDATA.ID = OLDDATA.ID

  Your append query should look similar to this

  INSERT OLDDATA SELECT * FROM NEWDATA

  WHERE NOT EXISTS(SELECT * FROM OLDDATA WHERE OLDDATA.ID = NEWDATA.ID)