sql server 2000 delete top 10000 lines 5

[tempo1]

Hi everyone,
In order to show first 10000 rows from a table i write

select top 10000 * from mytable

but is there a similiar command for "delete" ? something like:

delete from my table top 10000

Thanks a lot.

 

[SQLSister]

No not like that

delete test1
-- select *
from test1 t join
(select top 1000 idfield from test1 order by idfield) a
on t.idfield = a.idfield

Never ever under any circumstances use a top 1000 (or top anything else) without an order by clause or you may not get consistent results. This is particulalry imporatnat in a delete statement - you wouldn't want to think you were deleting one set of records and end up deleting a different set. Note I have a commented out select in there. Always run the select first to make sure you are getting what you intended. For instance if you joined on some field that wasn't unique - you could get more records than you thought you were gettting.

Questions about posting. See faq183-874

 

[ESquared]

If ordering doesn't matter, you can also:

SET ROWCOUNT 10000

DELETE TableName

SET ROWCOUNT 0

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]

 

[SQLDenis]

>>Note I have a commented out select in there.

yeah I once forgot to comment it out after I ran the select, can you guess what happened?

delete test1
select *
from test1 t join
(select top 1000 idfield from test1 order by idfield) a
on t.idfield = a.idfield

that is right all gone ;-)

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[tempo1]

Thank you very much. I only want to explain why ordering didnt matter.
I have 150000 rows which i want to copy to an excel sheet. I thought i would pass 10000 at a time and then delete 10000 each time. Hence the order didnt matter but i do understand the risk so i'll read your consult carefully and act accordingly.
Do you think there is a better way to transfer 150000 rows
to Excel ?
Thanks

 

[bborissov]

Be careful,
What version of Excel the user should use? In Excel 97 there will be no more than 16384 rows, in later versions, smaller than 2007, they should be 65535 rows in single sheet, only in 2007 I think you could have 256 000 rows (I'll check this tomorrow to be exactly sure)

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[SQLDenis]

2007 goes up to 2 billion rows

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[bborissov]

Thanks Denis,
Because I can't install Office 2007 on my computer (I use Win Server 2003 with terminal server installed) I couldn't check this.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[SQLDenis]

Excel 2007 files are zipped, check this out

http://sqlservercode.blogspot.com/2007/06/excel-2007-files-are-winzipwinrar-files.html

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[bborissov]

Yep,
I know the are simple zipped XML files.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[ESquared]

yeah I once forgot to comment it out after I ran the select, can you guess what happened?

delete test1
select *
from test1 t join
(select top 1000 idfield from test1 order by idfield) a
on t.idfield = a.idfield

1. Never, never, NEVER put the comment in front of the select. Put it in front of the data modification statement.

2. Use an alias in the delete or update clause, NOT the table name. This will help protect you no matter what you end up running, even if you run just the delete line by itself (unless you happen to have a table named by the alias you use, so don't use that table as an alias or don't name tables that short).

select *
[red][b]--[/b][/red] delete [s]test1[/s] [b][red]t[/red][/b]
from test1 t join
(select top 1000 idfield from test1 order by idfield) a
on t.idfield = a.idfield

To run the delete/update once you're satisfied with the select results, highlight starting just after the begin comment, through the end of the query, and hit F5. Don't remove the comment at any time.

And an unrelated #3 that comes to mind:

If you have a big .sql file you're working with that should NOT be run in its entirety, here's another runaway code protection. First,

CREATE DATABASE trash

Then, at the top of your SQL file:

USE DATABASE trash
BEGIN TRAN
GO

This way, if you hit F5 accidentally, you won't have the same kind of problems, unless somewhere in your script you have USE DATABASE commands and COMMIT TRANs--a practice I avoid, at least the use database command in dev scripts...

Of course, this also won't protect you if you use object names, but that's a reason to avoid object names in your dev/maintenance scripts. Use object names in your production code.

This little technique has saved me a headache several times.

a simple ROLLBACK TRAN and switch back to the database I want and I'm not left trying to correlate 100 errors + random statements that did *something* to the lines of script to see if I did anything seriously bad.

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]

 

[tempo1]

Hi everyone,
I'm having Excel 2003. i knew it was capable of 60000 rows (how about width ?), I already did send 60000 rows at a time but this time it stuck (maybe width of rows ?) so i decided to make it 10000 at a time as i described above.
Thanks a lot Borislav for your efforts and thank you all for your vital help.

 

[tempo1]

And to be more precise...
It was not the Excel that got stuck, my email server couldnt transfer that Excel page.. Sorry for having not described the problem correctly

 

[chamilz]

2007 goes up to 2 billion rows

I think Excel 2007 support 1 million rows.

Here are some new features.

Office Excel 2007 supports up to 1 million rows and 16 thousand columns per worksheet. Specifically, the Office Excel 2007 grid is 1,048,576 rows by 16,384 columns, which provides you with 1,500% more rows and 6,300% more columns than you had available in Microsoft Office Excel 2003. For those of you who are curious, columns now end at XFD instead of IV.

Instead of 4 thousand types of formatting, you can now use an unlimited number in the same workbook, and the number of cell references per cell are increased from 8 thousand to limited by available memory.

To improve the performance of Excel, memory management has been increased from 1 GB of memory in Microsoft Office Excel 2003 to 2 GB in Office Excel 2007.

You will also experience faster calculations in large, formula-intensive worksheets because Office Excel 2007 supports multiple processors and multithreaded chipsets.

Office Excel 2007 also supports up to 16 million colors.

 

[SQLDenis]

oops my bad

here is some more info

The total number of available columns in Excel
Old Limit: 256  (2^8)
New Limit: 16k  (2^14)

The total number of available rows in Excel
Old Limit: 64k  (2^16)
New Limit: 1M  (2^20)

Total amount of PC memory that Excel can use 
Old Limit: 1GB
New Limit: Maximum allowed by Windows

Number of unique colours allowed a single workbook
Old Limit: 56 (indexed colour)
New Limit: 4.3 billion (32-bit colour)

Number of conditional format conditions on a cell
Old Limit: 3 conditions
New Limit: Limited by available memory

Number of levels of sorting on a range or table
Old Limit: 3
New Limit: 64

Number of items shown in the Auto-Filter dropdown
Old Limit: 1,000
New Limit: 10,000

The total number of characters that can display in a cell
Old Limit: 1k (when the text is formatted)
New Limit: 32k or as many as will fit in the cell (regardless of formatting)

The number of characters per cell that Excel can print
Old Limit: 1k
New Limit: 32k

The total number of unique cell styles in a workbook (combinations of all cell formatting)
Old Limit: 4000
New Limit: 64k

The maximum length of formulas (in characters)
Old Limit: 1k characters
New Limit: 8k characters

The number of levels of nesting that Excel allows in formulas
Old Limit: 7
New Limit: 64

Maximum number of arguments to a function
Old Limit: 30
New Limit: 255

Maximum number of items found by “Find All”
Old Limit: ~64k (65472)
New Limit: ~2 Billion

Number of rows allowed in a Pivot Table
Old Limit: 64k
New Limit: 1M

Number of columns allowed in a Pivot Table
Old Limit: 255
New Limit: 16k

Maximum number of unique items within a single Pivot Field
Old Limit: 32k
New Limit: 1M

Length of the MDX name for a Pivot Table item; also the string length for a relational Pivot Table
Old Limit: 255 characters
New Limit: 32k

The length at which fields’ labels are truncated when added to PivotTable; this also includes caption length limitations
Old Limit: 255
New Limit: 32k

The number of fields (as seen in the field list) that a single PivotTable can have
Old Limit: 255
New Limit: 16k

The number of cells that may depend on a single area before Excel must do full calculations instead of partial calculations (because it can no longer track the dependencies required to do partial calculations)
Old Limit: 8k
New Limit: Limited by available memory

The number of different areas in a sheet that may have dependencies before Excel must do full calculations instead of partial calculations (because it can no longer track the dependencies required to do partial calculations)
Old Limit: 64k
New Limit: Limited by available memory

The number of array formulas in a worksheet that can refer to another (given) worksheet
Old Limit: 65k
New Limit: Limited by available memory

The number of categories that custom functions can be bucketed into
Old Limit: 32
New Limit: 255

The number of characters that may be updated in a non-resident external workbook reference
Old Limit: 255
New Limit: 32k

Number of rows of a column or columns that can be referred to in an array formula
Old Limit: 65,335
New Limit: Limitation removed (full-column references allowed)

The number of characters that can be stored and displayed in a cell formatted as Text
Old Limit: 255
New Limit: 32k

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[grnzbra]

How would one do it if they wanted to delete the top x rows of a particular group of records in the table

delte top x
from table A
order by a.datemodified DESC, a.void DESC
where a.ASC = @ASC AND a.SN = @SN AND a.AN = @AN

This thing is grouped by ASC, SN and AN for which I have specific values. datemodified and void may be null in all records or just some records.

It's kinda like "Pick one, any one, preferably one we know is the last one modified or one that is void, but if that info isn't available, any one in the group will do"

By the way, I just ran an insert top 1 into another table. What I am trying to do is purge this table of all these records before sending the remaining records to another table.