Rollback insert statement.

Question

Hi,

I have inserted some records in one of my table which are upto 5000.

But now i realize that these records should not be inserted in this table. I can't delete the records one by one. Or can't distinguish the inserted records for the previous records in the table.

How can i rollback this insert statement, so that my table return to its original form.

(I red that these is a way using log. But i don't know erectly how to do it.)

Thanks.

Answer 1

Assuming you were not inside a transaction when you did this, you're going to find the process painful. Not sure what flavour of DBMS you're interested in, but here's some info on SQL Server 2005:

http://stackoverflow.com/questions/1065531/rollback-transaction-using-transaction-log

Essentially, the easiest way to do this is to restore your DB from a backup that predates your inserts.

Answer 2

I am guessing the inserts statements have been committed to the database else you could simply do a ROLLBACK;

Do you have an audit table? If there is an audit table you would be able to identify the rows inserted during a particular datetime and then delete them...

Answer 3

Are there any features of the inserted rows that make them distinguishable from all of the others? For example is there an inserted date, or a numeric identity column? If so, just do a delete based on that criteria.

Other than that, butterchicken is right, it's probably a bit painful. But I've never rolled back using a transaction log like that before.