archiving records - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

archiving records

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-11-08 : 07:43:56

Rick writes "I have created 2 databases, operations and archive. I have an orders table set up in each of the 2 databases. The 2 tables are identical in structure. I use the operations database to store the current data and the archive to store the historic data. Once a record in the operations table has been marked as completed I want to move it to the archive database. This process doesn't have to be completed immediately, it could run weekly and move all the completed records at one time.

Is there a quick and easy way to move the records in this manner? There are quite a few columns in the table and the typical Insert statement for this would be quite long. I would like to see if there is a simple way to handle this.

Thanks for your assistance.

Rick H."

Kristen
Test

22859 Posts

Posted - 2004-11-08 : 09:29:20

Unless we are talking about millions of rows I reckon you are best using:


BEGIN TRANSACTION
INSERT INTO ARCHIVE_DB.dbo.MyOrdersTable
SELECT *
FROM OPERATIONS_DB.dbo.MyOrdersTable
WHERE Status = 'COMPLETED'
IF @@ERROR <> 0 GOTO DoRollback

DELETE D
FROM OPERATIONS_DB.dbo.MyOrdersTable D
     JOIN ARCHIVE_DB.dbo.MyOrdersTable A
         ON A.MyPK = D.MyPK
WHERE D.Status = 'COMPLETED'
IF @@ERROR <> 0 GOTO DoRollback

COMMIT
GOTO AllDone

DoRollback:
ROLLACK
AllDone:

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources