Solved

MS SQL - trim a table to 100,000 records

Posted on 2009-05-07
9
298 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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

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 SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
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: …

746 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

11 Experts available now in Live!

Get 1:1 Help Now