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.
| Author |
Topic |
|
Manoj Kumar
Starting Member
9 Posts |
 Posted - 2006-09-26 : 12:48:56
|
| Hello All,I am involved in a project wherein i have to deletion thousand of rows from the tables...The database is SQL SERVER 2000.I have done the similar project in Oracle....where i build the following Procedure...*************************************************************CREATE OR REPLACE procedure delete_accountasbegin loop DELETE FROM AUDIT_ACCOUNT WHERE Creation_Date <TO_DATE('2005/01/0100:00:00','YYYY/MM/DD HH24:MI:SS')and rownum<=1000; exit when sql%rowcount=0;commit; end loop;end;*********************************************************Now I have to made this PROC in SQL SERVER....I am facing problem with the following clauses....1) rownum<=1000.........Seems there is no rownum used in SQL SERVER2) exit when sql%rowcount=0.......Seems there is nothing like sql% rowcount in SQL SERVER....The region why i am using these variable is that...There is a concept of ROLLBACK Segment in Oracle...And if I try to delete thousands of records at the same time....the ROLLBACK Segment would fill up and the procedure would fail.....That is why I included COMMIT after every 100 records..Request you al if there is a way to implement this in SQL SERVER..Thanks in Advance..Regards,Manoj Kumar |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2006-09-26 : 13:03:02
|
| As far as I know, the concept of ROLLBACK is there in SQL Server but the said limit is not applicable (less than 1000 records at a time).So, you can directly do this without giving rownum condition.DELETE FROM AUDIT_ACCOUNT WHERE Creation_Date < Convert(Datetime,'01/01/2005',101)FYI:1) There is now RowNum in SQL server but you can mimic it with Identity column (and plenty other ways)2) There is no internal cursor (like SQL in ORACLE) which is accessible programmatically...so SQL%Rowcount has no counterpart in SQL Server.Harsh AthalyeIndia."Nothing is Impossible" |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-09-26 : 13:17:25
|
| http://weblogs.sqlteam.com/tarad/archive/2003/10/15/305.aspxTara Kizer |
|
|
|
Kristen
Test
22859 Posts |
|
|
|
|
|
ROLLBACK Segment problem