Solved

SQL Server 2005 DELETE Query

Posted on 2014-10-10
5
238 Views
Last Modified: 2014-11-24
I have a sql server 2005 database that has one table in particular with approx. 800,000 rows. I need to delete all but one row of this table for troubleshooting purposes.

Looking at the table there appears to be a column called 'SEQ' which contains numbers that appear to be unique to each row. I wish to pick one of these numbers randomly and delete all rows except that one.

What would be the best way to achieve this goal?
0
Comment
Question by:dowhatyoudo22
5 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 total points
ID: 40373065
it depends on what you call "best"

if this is a test table, just rename it, recreate it with the old name, and copy just 1 record back. this should assign it a new SEQ number , unless you want to keep the "exact" value, in which case: http://technet.microsoft.com/en-us/library/aa259221%28v=sql.80%29.aspx

to get 1 random record:
select top 1 * from yourtable order by newid()
0
 
LVL 11

Expert Comment

by:John_Vidmar
ID: 40373073
Assuming there is no referential-integrity that would prevent you from deleting a record:
-- grab the record(s) into a temporary table
SELECT * INTO #temp FROM YourTable WHERE something = whatever

-- delete all rows, avoiding transaction-log
TRUNCATE TABLE YourTable

-- insert the record(s) captured earlier
INSERT YourTable SELECT * FROM #temp

Open in new window

0
 
LVL 48

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 250 total points
ID: 40373081
You can save a record randomly (use Guy's suggestion) and the TRUNCATE the table (deletes all records without logging) and then put back the saved record:
SELECT TOP 1 * 
INTO #tempTable
FROM yourTable 
ORDER BY newid()

TRUNCATE yourTable

INSERT INTO yourTable
SELECT * FROM #tempTable

DROP #tempTable 

Open in new window

0
 
LVL 25

Expert Comment

by:Mohammed Khawaja
ID: 40373413
I agree with Vitor, his solution is elegant and easy to deploy.  If it makes you feel better, you could run the query "select top 1  * from yourtable", copy the information to notepad or save the results to a file and then truncate the table.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40373581
For the record, truncating a table does *not* "avoid logging", it reduces it.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

808 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