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 |
JJkk
Starting Member
2 Posts |
Posted - 2006-09-14 : 16:31:33
|
Hi AllCan any one help me in converting this from WHICH IN ORACLE to MS SQL SERVERsqlplus -s $UserId/$PassWord@$DataBase <<EOSQL> $LogFile (This is the Connection String) set serveroutput on Declare tempCnt Number:=0; totDelCnt Number:=0; Begin Loop $DelStmt tempCnt := tempCnt+ SQL%ROWCOUNT ; totDelcnt := totDelCnt+ SQL%ROWCOUNT ; If SQL%NOTFOUND Then Exit; End if; If tempCnt >= 50000 Then Commit ; tempCnt:=0 ; End if ; End Loop; Commit ; dbms_output.put_line('No of Recs Deleted From $TableName: '|| totDelcnt); End; / exit EOSQL |
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-09-14 : 17:31:36
|
For those of us who don't know Oracle very well, could you explain in words what it does so that we can come up with a SQL Server solution for you?Tara Kizer |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-09-15 : 04:12:43
|
If you are trying to delete rows from a table, and commit every 50000 rows, then I think my suggestion below would do, with a couple of caveats.(Not sure why I don't see any DELETE statement or declaration/assignment of $TableName in your code, but I guess its a template or somesuch??)DECLARE @intRowCount intSELECT @intRowCount = 1 -- Force first loop iterationWHILE @intRowCount >= 1BEGIN SET ROWCOUNT 50000 -- Restrict ALL subsequent operations to 50,000 rows BEGIN TRANSACTION DELETE FROM MyTable WHERE ... SELECT @intRowCount = @@ROWCOUNT -- Rows actually deleted COMMIT SET ROWCOUNT 0 -- Remove the restrictionEND The Begin Transaction / Commit is not actually doing anything useful, so you can leave that out (unless you have a more complicated scenario where you might need to be able to rollback multiple actions).However, if there is an OUTER Transaction in operation then this will not commit (it will "save the work so far", but it won't actually be committed until the outer transaction completes, so will still use up log space, lock resources etc.)Something to be aware of!!You could work around by checking the transaction level @@TRANSCOUNT and forcing a commit of all levels - but any outer transaction is probably going to be very upset about that - particularly if it tries to ROLLBACk and can't!Kristen |
|
|
JJkk
Starting Member
2 Posts |
Posted - 2006-09-15 : 13:07:25
|
Thanks for reply with more description.Do u know what's the statement or function where in we can display the result once all the transaction or procedure is done, which in oracle serveroutput on.Thanks once again |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-09-15 : 13:14:57
|
The default isSET NOCOUNT OFFwhich displays informational messages - like "50,000 row(s) processed" - that will show the number of records that are deleted. But its a bit useless to be honest!More typically in Procedures we useSET NOCOUNT ONwhich turns the no-ouput ON - double negative, very complicated. This STOPs the informational message.There is a system variable that will tell you the number of rows processed, you can display that how-you-wish:DECLARE @intRowCount int, @intTotalRowsSELECT @intRowCount = 1, -- Force first loop iteration @intTotalRows = 0WHILE @intRowCount >= 1BEGIN SET ROWCOUNT 50000 -- Restrict ALL subsequent operations to 50,000 rows BEGIN TRANSACTION DELETE FROM MyTable WHERE ... SELECT @intRowCount = @@ROWCOUNT, -- Rows actually deleted @intTotalRows = @intTotalRows + @intRowCount COMMIT SET ROWCOUNT 0 -- Remove the restrictionENDSELECT [Total rows deleted] = @intTotalRows -- Resulset outputPRINT 'Total rows deleted ' + CONVERT(varchar(20), @intTotalRows) -- Informational message alternative Kristen |
|
|
|
|
|
|
|