Moving rows from one table to another

I am moving records from one database to another, as a part of archiving process. I want to copy the rows to destination table and then delete the same rows from the source table.

My question is, what is the most efficient way to do a check if the first insert was successful before deleting the rows.

My idea is this, but I feel there is a better way:

@num_records=select count(ID) from Source_Table where (criteria for eligible rows)

insert * into Destination_Table where (criteria for eligible rows)

if ((select count(ID) from Destination_Table where (criteria) )=@numrecords)

delete * from Source_Table where (criteria)

Is it better/possible to combine it with RAISERROR function? Thank you!

If your archive table does not.

• Have enabled triggers defined on it.

• Participate on either side of a FOREIGN KEY constraint.

• Have CHECK constraints or enabled rules.

You could also do it in one statenent.

DELETE FROM source_table
OUTPUT deleted.Foo,
       deleted.Bar,
       SYSUTCDATETIME()
INTO archive_table(Foo, Bar, archived)
WHERE  Foo = 1;

This will either succeed or fail as a unit and also avoids possible race conditions with rows being added in between the INSERT to the archive and DELETE (though your WHERE clause may well make this extremely unlikely anyway).

StackExchange Database Administrators Q#31460, answer score: 19