Archiving data from one table to another.... 1

[Mayoor]

Hi I'm going to be archiving data from one table to another based on a set of rules.

The records must be older than 3 months AND (
The complete field = 0 OR (The complete = 1 AND the downloaded field = 1))

I am going to use a stored procedure to do this and call it from an ASP script.

Firstly I need to know how to write the SQL which will pull out a recordset of records to be archived then delete the records from the table and then insert them into the archived table.

Can anyone advise me of the best way to go abot doing this?

Any help would be much appreciated!

Mayoor

 

[SQLSister]

First, your process is not right. You perform an insert of the records which meet the criteria. Then you delete the records inthe original table which have been moved by using a join to the archive table. You need to stop thinking about pulling a recordset, then manipulating the data as this is extremely inefficent and can be done more efficeintly using one set-based statement. If you aren't sure how to do this look at Insert and delete in Books online, it will show you how to use another table in the statement.

Another thing you need in this stored procedure since you have multiple processes happening is to use transaction porcessing, so that everything rolls back if one part fails.

 

[Mayoor]

ahh brilliant, that sound like a sensible idea.

I have found some code to insert the records

INSERT archive(field1, field2, field3)
SELECT field1, field2, field3
FROM current
WHERE datefinished < getdate()

and this is the code to delete

Delete (au_id, au_lname, au_fname)
FROM current INNER JOIN
archive ON current.ID = archive.ID
WHERE

Will I be able to run the delete statement straight after the insert statement?

 

[SQLSister]

I think all you need is

Delete FROM current INNER JOIN
archive ON current.ID = archive.ID

Yes you should be able to run it in the same stored procedure after the insert. I would add error checking because there is no need to run it, if the insert failed for some reason.

 

[Mayoor]

Yep have found an excellent article on error handling and rolling back the procedure if the insert fails!

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlpro2k/html/sql00f15.asp

cheers SQLSister