Post reply

update archivedata base from active database

  • November 29, 2007 at 1:43 am

    #184465

    I want to update the archive database from the record in the active database.. My primary key is rowguid and if the record is existing, the record will be update and if theres no existing record the record will be insert.

    And if it is possible to put it in a trigger..

  • November 29, 2007 at 4:40 am

    #755324

    here's how I do it as a scheduled job; i don't need instant updates via a trigger:

    --new records first

    INSERT INTO ARCHIVETABLE(list_of_Columns)

    SELECT list_of_Columns from ACTIVETABLE

    LEFT OUTER JOIN ARCHIVETABLE ON ACTIVETABLE.GUIDID = ARCHIVETABLE.GUIDID

    WHERE ARCHIVETABLE.GUIDID IS NULL

    --now update where a specific column is different

    --so i dont update every row with the already existing value

    UPDATE ARCHIVETABLE

    SET ARCHIVETABLE.COLUMNNOTES = ACTIVETABLE.COLUMNNOTES

    WHERE ACTIVETABLE.GUIDID = ARCHIVETABLE.GUIDID

    AND ACTIVETABLE.COLUMNNOTES <> ARCHIVETABLE.COLUMNNOTES

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • December 2, 2007 at 4:22 pm

    #756328

    If there's a chance for a lot of updates, I wouldn't do a trigger. Better to do with the archive.

    I'd actually do a "with exists" test first

    if exists( select xx from archive where pk = x)

    update archive

    else

    insert archive

    I'm assuming you know how to structure these statements and this isn't homework. Please show us some work on your part.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply