Solved

MS SQL - trim a table to 100,000 records

Posted on 2009-05-07
9
300 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Incremental load example 2 52
Error when saving to sql table a '/' 5 27
Update a summary table with values from detail records 6 25
Addition to SQL for dynamic fields 6 37
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

786 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