MS SQL - trim a table to 100,000 records

Posted on 2009-05-07
Last Modified: 2012-06-27
I have a table that I use as a log of transactions.  The key is an incrementing number.

To keep the table from becoming too big, I would like to keep the most recent 100,000 records and remove the rest.  What T-SQL script would you recommend to do this?

Thank you
Question by:Tom Sage
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
LVL 60

Expert Comment

by:Kevin Cross
ID: 24330096
Since you have an autoincrementing ID and it is safe to presume that higher numbers are most recent records, you could do this:

;with cte as (
    select *,
    row_number() over (order by id desc) as rank
    from your_table_name
) delete from cte where rank > 100000;

Open in new window

LVL 60

Expert Comment

ID: 24330098
with cte
select *, ranking = row_number() over (order by additiondate desc) from transactions
delete from cte
where ranking > 100000
LVL 60

Expert Comment

ID: 24330102
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now


Author Comment

by:Tom Sage
ID: 24330404
I tried both scripts.  It seems they are keeping the oldest 100,000 records rather than the newest 100,000 records.

Would you please review and let me know if that is correct?

LVL 60

Accepted Solution

Kevin Cross earned 500 total points
ID: 24330470
They should NOT be.  Make sure you have the ORDER BY id_column_name DESC in the OVER clause.  This will order the records with highest (newest) IDs first.  If the newer records don't have newer IDs then please give example of data and which records should stay and which should go.  Give example using 5 old and 5 new -- don't need 100,000. :)


Author Closing Comment

by:Tom Sage
ID: 31579196
Works great!  Thanks for the prompt response!
LVL 60

Expert Comment

by:Kevin Cross
ID: 24330641

Glad that helped.

LVL 60

Expert Comment

by:Kevin Cross
ID: 24330679

Since Tim and I entered the same solution at the same time, you can feel free to split.  I am more than OK with that.

LVL 60

Expert Comment

ID: 24332903

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Grid querry results 41 86
kill process lock Sql server 9 81
Update a summary table with values from detail records 6 42
Database maintenance 36 141
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question