Post reply

Copying rows to an archive table

  • September 11, 2006 at 1:17 pm

    #114786

    Hi All:

    I'm trying to copy on-line production rows to a history table. I've tried DTS, but it will duplicate records on the history table. Basically, if the PK already exist, then either overwrite or ignore.

    The schemas are to be exactly alike. I.E.

    Table production:

    TransactionID Int 4 Identity

    Descriptions Char 20

    Table history

    TransactionID Int 4 Identity

    Descriptions Char 20

    I've fooled around with some of the options on the export without any luck.

    Thanks for your help

    William

  • September 11, 2006 at 2:17 pm

    #659760

    Are you trying to Copy the entire production table to historical table?

    Are they in the same server/database if so then the best way is to use stored procedure

    Why do u have TransactionID Int 4 Identity in History table when u are using it to archive Production data to History table.

    If you have to use DTS load it in Staging table (assuming Different server and DB) and using conditional statement to decide on "Insert" or "Update".

    Hope this points you in right direction.

    Thanks

    Sreejith

  • September 11, 2006 at 2:21 pm

    #659763

    it is the same server and same database. Maybe your point is well taken and I really don't need to use the Identity on the history table, as the only data that is going to be written to it will be the montly purge to history job.

    I'll give it a try

    Thanks

    William

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

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