Solved

MS SQL - trim a table to 100,000 records

Posted on 2009-05-07
9
299 Views
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
0
Comment
Question by:MisterT25
  • 4
  • 3
  • 2
9 Comments
 
LVL 59

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

0
 
LVL 60

Expert Comment

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

Expert Comment

by:chapmandew
ID: 24330102
genius!
0
 

Author Comment

by:MisterT25
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?

Thanks
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 59

Accepted Solution

by:
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. :)

Kev
0
 

Author Closing Comment

by:MisterT25
ID: 31579196
Works great!  Thanks for the prompt response!
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24330641
MisterT25,

Glad that helped.

Regards,
Kevin
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24330679
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
 
LVL 60

Expert Comment

by:chapmandew
ID: 24332903
:)
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

919 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now