Purge Query - SQL Server Forums

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)

Purge Query

Author

Topic

dpieper
Starting Member

2 Posts

Posted - 2008-01-23 : 15:56:04

I am an intermediate self taught SQL database administrator and need some help with a purge query. I have purge query that is designed to purge record from 2006 from said table. The table in question however is over 2 million records. The last time the purge query was run I had to force quit after 12 hours. Is there a way to run the purge query on 100000 rows at at time using Rowcount that I am not aware of.

Query in question:

DELETE Year([Invoice_Date]) AS InvYear, dbo_Page_Card.*
FROM dbo_Jobs INNER JOIN dbo_Page_Card ON dbo_Jobs.Job_ID = dbo_Page_Card.Job_ID
WHERE (((Year([Invoice_Date]))<(Year(Now())-1)));

HeliosRex
Starting Member

7 Posts

Posted - 2008-01-23 : 16:24:11

You could use SET ROWCOUNT

SET ROWCOUNT 10000
GO

DELETE Year([Invoice_Date]) AS InvYear, dbo_Page_Card.*
FROM dbo_Jobs INNER JOIN dbo_Page_Card ON dbo_Jobs.Job_ID = dbo_Page_Card.Job_ID
WHERE (((Year([Invoice_Date]))<(Year(Now())-1)));

Regards,
HeliosRex

dpieper
Starting Member

2 Posts

Posted - 2008-01-24 : 09:34:40

Thanks I justs wasn't positive on the syntax.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-01-24 : 09:40:55

Now() ? Are you using MS SQL Server ?

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources