SQL Server Performance

Writing a move and update storedprocedure. Sql2000

Discussion in 'General DBA Questions' started by Player, Jul 30, 2004.

  1. Player New Member

    Hi,


    I need to write a stored Procedure that will be able to write to a table, but before
    it writes to the table it needs to check if the record exists and if so it must
    first move it to a History table and then update the record in the first table where it is writing to.

    Like

    Insert into Table
    if exists (select * from table where a.id1 = 123)
    then if it does not exist it must write it into "table"
    but if it does exist then it must move it to "tablehistory" and then just update the
    record in the "table" table.

    Any suggestions?
  2. derrickleggett New Member

    You should probably have a test with a couple subqueries for this if you want to avoid recompiles and have the fastest execution plan.

    First procedure:

    IF (SELECT column_list FROM table WHERE id1 = 123) IS NOT NULL
    BEGIN
    EXEX TableUpdate
    END
    ELSE
    BEGIN
    EXEC TableInsert
    END


    TableUpdate would insert to tablehistory and update changed fields.
    TableInsert would just insert new row(s).



    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  3. Player New Member

    Thanks for the help.

Share This Page