Lokesh Vij (9/19/2012)

---

There is a requirement, details as below:

1) Copy data from transaction table (around million records inserted daily) into a archival table
2) Delete data from transaction table
3) Step (1) - transactions can be minimally logged
4) Step (2) - all transactions completely logged.
5) There are 14 transaction tables; step (1) to (4) needs to be repeated for all transaction tables.

Here is the sample code (for one table)

-- assuming archival table already exists
-- Step 1: Copy data into archivalTbl from transactionTbl with minimal logging
INSERT INTO archivalTbl WITH (TABLOCK)
            (col1,
             col2,
             col3)
SELECT col1,
       col2,
       col3
FROM   transactionTbl;

-- Step 2: Delete data from transactionTbl will full logging
DELETE FROM transactionTbl; 

Can someone please advise any best way to achieve this?

Thank you.

Lynn Pettis (9/19/2012)

---

First, INSERT INTO will be fully logged, not minimally logged (iirc).

Lynn Pettis (9/19/2012)

---

If you are adding and deleting nearly 2,000,000 rows of data, then partitioning may be the what is needed for the archiving process. Depending on the data, it may be possible to establish a slinding window scenerio for quickly archiving data.

Lokesh Vij (9/20/2012)

---

Hi Lynn
I am using table locking option TABLOCK, this support minimal logging.

Lynn Pettis (9/20/2012)

---

Care to show us a reference that supports this assertion? I haven't been able to find one, but I'll keep looking.

When importing data into a heap by using the INSERT INTO <target_table> SELECT <columns> FROM <source_table> statement, you can enable optimized logging and locking for the statement by specifying the TABLOCK hint for the target table. In addition, the recovery model of the database must be set to simple or bulk-logged. For more information, see INSERT (Transact-SQL).

Lokesh Vij (9/19/2012)

---

There is a requirement, details as below:

1) Copy data from transaction table (around million records inserted daily) into a archival table
2) Delete data from transaction table
3) Step (1) - transactions can be minimally logged
4) Step (2) - all transactions completely logged.
5) There are 14 transaction tables; step (1) to (4) needs to be repeated for all transaction tables.