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.