1

I have an SSIS package that was run unintentionally, which appended data to a SQL Server table. Is there a way to delete the last 100 rows which were added to the table? The table is:

CREATE TABLE dbo.Users
(
  name VARCHAR(100),
  phone_number VARCHAR(30)
);

No IDENTITY column, no date created column.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
anm
  • 1,865
  • 3
  • 19
  • 28
  • 7
    Do you have some sort of unique identifier/Id that will allow you to determine the last 100 rows? Posting your table structure would probably be helpful to answer this question. – Taryn Oct 22 '13 at 14:38
  • No it does not, the table consists of two columns name and phone number for 'USERS'. – anm Oct 22 '13 at 14:46
  • 7
    Unless you have something to define a chronological order (a `DATETIME` column, an ever-increasing `ID` column) then you cannot really find out which are the "last" 100 rows inserted – marc_s Oct 22 '13 at 14:47
  • 4
    @bluefeet you mean relational tables aren't FIFO buffers? – swasheck Oct 22 '13 at 14:48
  • Do you have any history or logging tables? – UnhandledExcepSean Oct 22 '13 at 14:50
  • 3
    Did it create duplicates? If not, what was the source? Is there a way to leverage the source to figure out which rows you need to remove? – swasheck Oct 22 '13 at 15:00
  • If no backups then you *could* get this out of the log in a very similar way to here [How to recover deleted data from SQL Server](http://raresql.com/2011/10/22/how-to-recover-deleted-data-from-sql-sever/). If the log still contains those records. – Martin Smith Oct 22 '13 at 15:26
  • 5
    This might provide some [consolation](http://www.youtube.com/watch?v=lwx2ce_AyOE) – billinkc Oct 22 '13 at 16:56
  • http://stackoverflow.com/questions/4507509/how-to-view-transaction-logs-in-sql-server-2008 – TsSkTo Oct 23 '13 at 07:31
  • I agree with experts here. If you dont have a way of figuring out which row was inserted when (or an ID) then you really cant delete your last 100 rows. Change the table structre to add either column. – rvphx Oct 24 '13 at 06:29
  • one option is to modify the SSIS package to point to another table and execute it again to see what the 100 rows are and then compare it to the actual table where the rows ended up in. But this is not a guarantee. – Fuzzy Oct 29 '13 at 09:01

0 Answers0