MS SQL - trim a table to 100,000 records

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
Tom SageAsked:
Who is Participating?
 
Kevin CrossChief Technology OfficerCommented:
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. :)

Kev
0
 
Kevin CrossChief Technology OfficerCommented:
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

0
 
chapmandewCommented:
with cte
as
(
select *, ranking = row_number() over (order by additiondate desc) from transactions
)
delete from cte
where ranking > 100000
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
chapmandewCommented:
genius!
0
 
Tom SageAuthor Commented:
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?

Thanks
0
 
Tom SageAuthor Commented:
Works great!  Thanks for the prompt response!
0
 
Kevin CrossChief Technology OfficerCommented:
MisterT25,

Glad that helped.

Regards,
Kevin
0
 
Kevin CrossChief Technology OfficerCommented:
MisterT25,

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.

Kev
0
 
chapmandewCommented:
:)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.