0

I have 42,715,078 entries in one of my table, that I would like to delete TOP 42,715,000 rows (i want to keep just 78 entries).

Any one know who can I do that??

PS: I dont want to delete the table, just want delete the entries of table.

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
Vinnie
  • 1,053
  • 11
  • 31
  • 4
    You would be better off (in terns of logging) storing the 78 in a temp table then truncating the table and adding them back in. Please supply your table structure and the definition of `TOP`. As ordered by what? – Martin Smith Feb 22 '13 at 13:03
  • possible duplicate of [Bulk DELETE on SQL Server 2008 (Is there anything like Bulk Copy (bcp) for delete data?)](http://stackoverflow.com/questions/2126434/bulk-delete-on-sql-server-2008-is-there-anything-like-bulk-copy-bcp-for-delet) – gbn Feb 22 '13 at 13:05

3 Answers3

6

Probably your best bet is to select out the 78 rows you want to keep into a temporary table, then truncate the table and insert them back in.

SELECT * INTO #temp FROM TableName WHERE <Condition that gets you the 78 rows you want>

Or if you don't have a specific 78 rows

SELECT TOP 78 * INTO #temp FROM TableName

Then

TRUNCATE TABLE TableName

And last but not least

INSERT INTO TableName
    SELECT * FROM #temp

Doing it this way should be considerably faster depending on what condition you use to get the 78 rows and you avoid bloating the log as TRUNCATE is only minimally logged.

Kenneth Fisher
  • 3,692
  • 19
  • 21
1

We have an activity log that we truncate once a month. (We keep the monthly backups, so we can get back to any old data if we want to.) If your table is growing every month and you want to keep it small like we do with ours, you can set up a SQL Agent Job to run each month.

We only remove 5000 rows at a time to keep the load of the database, so this job runs every two minutes for an hour. That gives it enough time to remove all the oldest rows without locking the database. 

DECLARE @LastDate DateTime  -- We remove the oldest rows by month
DECLARE @NumberOfRows INT   -- Number of rows to keep

-- Set the Date to the current date minus 3 months.
SET @LastDate = DATEADD(MM, -3, GETDATE())

-- Since it runs on the first Saturday of each month, this code gets it
   back to the first of the monh.
SET @LastDate = CAST(CAST(DATEPART(YYYY, @LastDate) AS varchar) + '-' + CAST(DATEPART(MM, @LastDate) AS varchar) + '-01' AS DATETIME)

-- We use 5000.
SET @NumberOfRows = 5000 

DELETE TOP (@NumberOfRows) FROM MyTable WHERE Created < @LastDate
Narnian
  • 3,858
  • 1
  • 26
  • 29
0

I got it.

DELETE TOP (42715000) 
FROM <tablename>
WHERE <condition>

It worked so well!

Vinnie
  • 1,053
  • 11
  • 31
  • 4
    `TOP` is undefined in this case. There is no guarantee what 42715000 rows will be deleted. – Martin Smith Feb 22 '13 at 13:07
  • 7
    You're likely to blow the transaction log. Delete is fully row by row logged so you have 42million+ log entries... – gbn Feb 22 '13 at 13:07
  • As Martin says, no guarantees here. That top really should have an order by. – Bridge Feb 22 '13 at 13:16
  • @Bridge - `DELETE TOP` doesn't support an `ORDER BY` you need to use a CTE or derived table with a `TOP ... ORDER BY` and delete from that. – Martin Smith Feb 22 '13 at 13:18