SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Help in coverting oracle to SQL Server
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

JJkk
Starting Member

2 Posts

Posted - 09/14/2006 :  16:31:33  Show Profile
Hi All

Can any one help me in converting this from WHICH IN ORACLE to MS SQL SERVER




sqlplus -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

USA
36674 Posts

Posted - 09/14/2006 :  17:31:36  Show Profile  Visit tkizer's Homepage
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
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 09/15/2006 :  04:12:43  Show Profile
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 int
SELECT @intRowCount = 1 -- Force first loop iteration
WHILE @intRowCount >= 1
BEGIN
    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 restriction
END

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
Go to Top of Page

JJkk
Starting Member

2 Posts

Posted - 09/15/2006 :  13:07:25  Show Profile
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
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 09/15/2006 :  13:14:57  Show Profile
The default is

SET NOCOUNT OFF

which 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 use

SET NOCOUNT ON

which 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,
        @intTotalRows
SELECT @intRowCount = 1, -- Force first loop iteration
       @intTotalRows = 0
WHILE @intRowCount >= 1
BEGIN
    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 restriction
END
SELECT [Total rows deleted] = @intTotalRows -- Resulset output
PRINT 'Total rows deleted ' + CONVERT(varchar(20), @intTotalRows) -- Informational message alternative

Kristen
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000