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!