Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Query help - delete all but 50 rows of data

Posted on 2007-11-16
5
Medium Priority
?
159 Views
Last Modified: 2010-03-19
Hi:

What is the fastest way to delete all but 50 rows  of data from a database table? Thanks.
0
Comment
Question by:sdc248
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 17

Expert Comment

by:Chris Mangus
ID: 20301442
Do you care which 50 rows you keep?  If not:

Delete From myTable Where myPK Not In (Select Top (50) myPK From myTable)
0
 
LVL 10

Accepted Solution

by:
pai_prasad earned 500 total points
ID: 20302204
depends on the number of records in the table...
 if u have 100 recds above query is gud ./

if u have 2 millon recds.. i wud pref

Select Top 50*
into myTable_bkp
from myTable

Truncate MyTable

Insert into myTable
Select * from myTable_bkp

0
 
LVL 17

Assisted Solution

by:Chris Mangus
Chris Mangus earned 200 total points
ID: 20302285
Assuming the table doesn't have an identity value that needs to be retained this will work.  If you have an identity value you'll need to turn Identity Insert on if you put records back into myTable.
0
 
LVL 25

Assisted Solution

by:imitchie
imitchie earned 200 total points
ID: 20303831
seeing that you are "removing all but 50", this is probably a dev db. i assume there aren't any foreign keys to care about? you probably want the "latest" data then?

delete mytable where idcol < (
select min(idcol) from
(select top 50 idcol from mytable order by idcol desc) x)

you can want raw speed, nothing beats truncate. but it has two major drawbacks:
resets identity columns, and fails when certain constraints are defined (i.e. foreign keys)
it's   TRUNCATE TABLE <tablename> btw, you can drop the "from" in DELETE FROM but not the "table" from TRUNCATE TABLE
0
 

Author Comment

by:sdc248
ID: 20313292
Thanks guys. The "truncate table" command helps a lot. The old "delete from mytable where" method took forever to delete data from a couple of huge tables (200 million rows) and eventually failed because the transaction log grew rapidly and fill up the server.  Luckily these huge tables don't have identity columns or foreign keys.
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

There are some very powerful Dynamic Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a di…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Screencast - Getting to Know the Pipeline

610 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