Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Server 2005 DELETE Query

Posted on 2014-10-10
5
Medium Priority
?
245 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
[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 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1000 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 52

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 1000 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 70

Expert Comment

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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
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…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

705 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