Best Method to Move Records Between Like Tables (Archive Process) 3

[Nickela]

I am looking to perform the following tasks in order to "archive" records from a production table to an archive table (both tables with the same definition) and am not sure if it would be best to do this through UDFs or Stored Procedures.

-Select all of the records from the production table that have a bit field (checkbox) with a value = 1 from the production table
-Set the identity insert on in the archive table
-Insert these records into the archive table
-Delete out the records that were selected and inserted into the archive table from the production table.
-Turn off the identity insert on the the archive table.

I have started to do this by opening up a cursor containing the records that I am wanting to archive, and was looking to create a stored procedure to walk through the cursor, but I wasn't sure if this was the most efficient way to do this (500 - 2500) records each time.

I am working in SQL 2000. Any thoughts? Thanks for your input.

nickela

 

[PauloRico]

The fact that the archive table IS an archive table and will not have new records generated adhoc, means that the identity column need not be an 'IDENTITY' column, so you don't need to mess about turning IDENDITY INSERT on/off.

What I would do is create a Stored Procedure that -
(a) INSERTs records from the Production table to the Archive table.

(b) Deletes all records in the Production table that match records in the Archive table.

 

[JohnDTampaBay]

No need for a cursor. Set-based is the true path.

As PauloRico stated, you don't have to worry about the Identity-Insert issue either. The key column doesn't need to be IDENTITY, just the base datatype (integer?).

You can use the same WHERE clause for the INSERT-SELECT and the DELETE statements. It is a good idea to do the transfer in batches (500-2500). Something like this:

  INSERT INTO ArchiveTable
  SELECT TOP 2500 ColumnList
  FROM ProductionTable
  WHERE BitField = 1

  DELETE FROM ProductionTable
  WHERE BitField = 1

  --Loop as long as there are more rows to archive
  WHILE @@RowCount = 2500 BEGIN
    INSERT INTO ArchiveTable
    SELECT TOP 2500 ColumnList
    FROM ProductionTable
    WHERE BitField = 1

    DELETE FROM ProductionTable
    WHERE BitField = 1
  END

Good luck!

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[NoCoolHandle]

This is an interesting situation..

be very careful about the use of cursors here.. If you use a cursor, you will take allot longer doing the insert than a nice set based insert. You will also fill logs much faster.. (1 entry for each row in the table as well as 1 entry (per row) for each index the table is built on..

5 indexes = 6 log entry’s for each line X 10000 rows = 60000 log entries..

To solve a problem very similar to this the approach I ended up going with was a storedproc..

Inside the stored proc we found out what the largest primary key (identity field) value was and stored it in a local variable.

This was our start point for the Move..
The next step was an insert statement into a linked server using the largest pk value in the where clause to specify exactly what records we were moving and to help to make sure we knew which records needed to be deleted.

Because the "insert into table select * from table " inserts all records into the destination table and then builds all indexes as a single set of operations, it is infinitely faster than a bunch of single row inserts (cursor processing) which rebuild all indexes for each row being inserted....

Result for the 10000 row table 1 log for each row in the table 1 entry for each index (5) = 10005 log entries a saving of about 50000 log entries.. And quite a bit of time.

After we had moved the rows to the warehouse we then deleted all rows below the stored pk value. This ensured that no rows that were added after the process started were removed by mistake..

Hope some of this made sense

-- Just to toss another idea out there..

DTS with a text file as the output and a bulk insert to the destination table was even faster yet..



Rob

 

[Nickela]

Thanks everyone for the replies. They all were very helpful.

nickela