Delete or Select query based on row count

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Delete or Select query based on row count

Author

Topic

schinni
Yak Posting Veteran

66 Posts

Posted - 2003-03-10 : 19:07:39

I have a table which is very huge so i want to delete or selectthe rows based on row count,
i am wondering if that is possible
example some thing like these
select * from tabname
where coun(*)> 200000

that is my idea

chadmat
The Chadinator

1974 Posts

Posted - 2003-03-10 : 23:56:47

What is the rank based on?

You could do:
select top 200000 * from tabname

But i'm not sure if that's what your asking

-Chad

Bambola
Posting Yak Master

103 Posts

Posted - 2003-03-11 : 02:33:15

set rowcount 200000
select * from tabname
set rowcount 0

Same goes for delete.

Bambola.

schinni
Yak Posting Veteran

66 Posts

Posted - 2003-03-12 : 19:50:22

Bambola,
That is exactly what i want,
Thanks very much

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-03-12 : 20:02:38

Here is some useful code if you want to delete a bunch of records but in batches instead of all at once:

DECLARE @cnt int

SELECT @cnt = count(*) FROM Table1
WHERE datecolumn < (getdate() - 180) --get the count

SET ROWCOUNT 2000 --only process 2000 records at a time

WHILE @cnt <> 0
BEGIN
--the actual code would go here
DELETE FROM Table1
WHERE datecolumn < (getdate() - 180)

SELECT @cnt = count(*) FROM Table1
WHERE datecolumn < (getdate() - 180) --get the count again
END

SET ROWCOUNT 0

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources