Results 1 to 11 of 11
  1. #1
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721

    Unanswered: Moving Records for Archiving

    I've got two tables, one is an archive of the second (tables are identical). I'd like to migrate records from one to the other (as in, move, insert into one while deleting from the other).

    I know I can start a transaction, do an INSERT INTO...SELECT, followed by a DELETE, check rows affected, then closing with a commit transaction (or rollback if the counts don't match), but it seems as though I might be over thinking it. Is this considered the optimal approach?
    That which does not kill me postpones the inevitable.

  2. #2
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    No, not really. An alternate approach might be to insert into the historical tables with an insert date and update "current" table with an active flag. Your OLTP will always look for the active flag.

    If you use this approach, you're not deleting thousands of rows out of your tables, messing up your statistics, and blowing holes in your indexes during the middle of the day.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  3. #3
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    Right. I did write the an SP to UNION the two tables (origin and archive) if I needed to include history in my queries, and I don't much like it, so I can see what you mean. Just add an Archived flag to the origin table, then run a process regularly to insert non-archived records into the historical archive. In which case, I'll need to include that Archived flag in my indexes for optimization.

    If I go this route though, now that I reflect, I won't actually need an archive table (unless I simply want a physical backup - which is irrelevant with our nightly backup procedures). I suppose I could offload historical queries to the archive table, but historical research is rarely done, so the savings to the origin table would be negligable. With that, I'm going to have to re-evaluate my need for a second 'archive' table. Assuming historical research is not a regular activity, can you think of a reason why it would be beneficial to maintain a separate archive table if I implement an archive flag within the origin table?
    That which does not kill me postpones the inevitable.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You didn't mention volume...but I would say the indicator won't optimize well

    Also I imagine there would be more Update activity on the current and none on the history...so they might have different indexing strategies...

    MOO

    I'd go with 2
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    Volume.. right.. my bad. Currently there's a low amount of data (only about 8k records), but this will increase as the application is used (these records represent transactions sent to our payroll system, so you can imagine this growing quite large).

    So, Brett, you believe that two tables doing an INSERT INTO...SELECT and a DELETE wrapped in a Transaction would be optimal? Or is there an alternative SQL query that could accomplish the same job? I do understand the though about different indexes, it does make sense.

    I believe I'm just concerned that all the INSERTs execute, and the DELETEs don't even begin until after the INSERTs complete. Although, with a transaction, the issue is moot I suppose, since all of the INSERTs and DELETEs can rollback if an issue is encountered. I realize I could do a 1-to-1 migration, but it would require starting and committing a transaction for every record being archived, and that doesn't seem efficient.
    That which does not kill me postpones the inevitable.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well, I guess if you want to track historical views of the data (a common practice), I would use a trigger to move to historical data, so I would not be bound to anyone process....

    With that said, it sounds like you only want to keep the last change...right?

    I would keep them all....

    What Business need is this meant to support?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    If you are going to delete from the production table, your delete statement should include a link to the archive table on the primary key. That way, you ensure you do not delete any records which have not been moved to the archive table.

    delete
    from production
    inner join archive on production.pkey = archive.pkey
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    Ok.. The requirement is that I keep a record of the transaction sent to payroll for umpteen years. To smooth this out a bit, I was going to archive anything over 12 months into an archive table, removing the original record from the original table once it had been moved.

    I figured as the original table grows, queries against the original table will take longer unless I do something. Hence, the creation of the archive table. Now, historical research is only done by administrators of the system, so I'm not worried about that, I'm more concerned about the primary transaction table since it's hit pretty frequently. So I figured I'd offload transactions over 12-months to the historical table to try to keep things clean, but I wanted to determine the most safe and efficient means of migrating those records.

    If I have to do it in two queries, that's fine, I just wasn't sure if I was on the right track.

    Blindman.. I like that delete, I didn't even think of doing it that way.

    Code:
    BEGIN TRAN T1
    INSERT INTO tblTransArchive SELECT * FROM tblTrans WHERE dtTimeStamp < DATEADD(m, -12, GETDATE())
    DELETE tblTrans FROM tblTrans T INNER JOIN tblTransArchive TA ON T.iTransID= TA.iTransID
    COMMIT TRAN T1
    This code works, and I'm fine with it, as long as it's the optimal way to do it.
    That which does not kill me postpones the inevitable.

  9. #9
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    Here's the SP I created based around that:

    Code:
    CREATE PROCEDURE [dbo].[spArchiveTrans] AS
    
    DECLARE @InsertCount int
    DECLARE @DeleteCount int
    
    BEGIN TRAN T1
    INSERT INTO tblTransArchive SELECT * FROM tblTrans WHERE dtTimeStamp < DATEADD(m, 0, GETDATE())
    SET @InsertCount = @@ROWCOUNT
    IF @@ERROR = 0
    BEGIN
      IF @InsertCount > 0
        BEGIN
          DELETE tblTrans FROM tblTrans O INNER JOIN tblTransArchive OA ON T.iOrderID = TA.iOrderID
          SET @DeleteCount = @@ROWCOUNT
          IF @@ERROR = 0
          BEGIN
            IF @InsertCount = @DeleteCount
              COMMIT TRAN T1
            ELSE
            BEGIN
              ROLLBACK TRAN T1
              RAISERROR('Archive totals did not match.  Transactions rolled back.', 16, 1)
            END
          END
          ELSE
          BEGIN
            ROLLBACK TRAN T1
            RAISERROR('Error removing transactions from origin.  Transactions rolled back.', 16, 1)
          END
        END
        ELSE
        BEGIN
          --Nothing to do
          ROLLBACK TRAN T1
        END
    END
    ELSE
    BEGIN
      ROLLBACK TRAN T1
      RAISERROR('Error inserting transactions into archive.  Transactions rolled back.', 16, 1)
    END
    GO
    That which does not kill me postpones the inevitable.

  10. #10
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    Why don't you run a test with that archive flag idea first? Compare that to the performance issues all this moving of data and testing is going to cause you. You really want to nail this thing the first time around, or you'll be paying for it later.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  11. #11
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    Yeah.. I'll give that a shot. My only concern with the archive flag is that the data will always be there intertwined with the active data. So every request for active data will have to sift through archived records. If I run a cleanup procedure every night, the performance impact is neglegable. Alternately, I'll have to find all the queries that hit the original table and add another flag to the query to ensure it pulls the correct columns, which I'm not complaining about, just concerned about missing one.
    That which does not kill me postpones the inevitable.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •