Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

MS SQL - trim a table to 100,000 records

Posted on 2009-05-07
9
Medium Priority
?
308 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:Tom Sage
  • 4
  • 3
  • 2
9 Comments
 
LVL 60

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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:Tom Sage
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 60

Accepted Solution

by:
Kevin Cross earned 2000 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:Tom Sage
ID: 31579196
Works great!  Thanks for the prompt response!
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 24330641
MisterT25,

Glad that helped.

Regards,
Kevin
0
 
LVL 60

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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
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…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Loops Section Overview

926 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