Post reply

How do you duplicate a record in Stored Procedure

  • February 17, 2004 at 8:28 am

    #107058

    Hi

    How can I easily duplicate a record and store it in the same table from within a stored procedure.

    I need to provide the SP with two parameters, ID of existing record and ID of new record. I then need to create a new record with the new ID, copying the values from the existing record.

    Any ideas

     

    Ben

  • February 17, 2004 at 9:32 am

    #494392

    Why do you need a different ID?

    (An update of the Fields wound't DO?)

    in any case...

    Insert into TblName (ID,fld2,fld3,....)

    Select @newID,fld2,fld3,... From TblName Where ID = @oldID

    If @@ERROR <> 0

     begin

     Rollback tran

    return

    end

    Delete from tblName Where ID = @oldID

    If @@ERROR <> 0

     Rollback tran

    Else

    Commit Tran

    Return 


    * Noel

  • February 18, 2004 at 2:13 am

    #494477

    Hi noeld,

    I've been asked how to do this for a colleague who basically wants to create an audit trail on an records that have been edited on a given table. He wants to keep the old versions of the record in the same table.

    I forgot to mention that building a complete list of fields would be fairly impracticle as the table is huge.

    I think a better way would be to have a seperate audit table that gets populated via an update trigger on the first table, but it would be nice to know how to duplicate the record anyway.

    Thanks

    Ben

  • February 23, 2004 at 9:22 am

    #495171

    Ben,

    I would go your route using an audit table.  That's what we do where I work.  We store the old and new information together in the same record, what form/page the change was made on, date, by who, etc.

    I would think if you stored the old and new (revised) records in the same table you will eventually have to address the issue of filtering for the correct record.  And wouldn't that affect all your sp's?

    michael

  • February 23, 2004 at 9:53 am

    #495182

    Definitely go for the Audit table.

    I cannot think of a solution to not build a list of fields.

    I can imagine this is cumbersome, but have a look in the Scripts section. There are some scripts that generate the list for you. Using those, it's just a question of Copy/Paste - ing the string of fields to your SP.

    I think this one should do the trick ...

    http://www.sqlservercentral.com/scripts/contributions/926.asp

Viewing 5 posts - 1 through 4 (of 4 total)

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