Solved

MS SQL - trim a table to 100,000 records

Posted on 2009-05-07
9
301 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

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
 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Pivot Query Problem 9 49
My Query is not giving correct result. Please help 5 53
SQL Server Insert where not exists 24 52
Parsing this XML works but the other one doesn't 9 26
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…
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

679 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