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
 Old Forums
 CLOSED - General SQL Server
 Help in coverting oracle to SQL Server

Author  Topic 

JJkk
Starting Member

2 Posts

Posted - 2006-09-14 : 16:31:33
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

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

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

Kristen
Test

22859 Posts

Posted - 2006-09-15 : 13:14:57
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
   

- Advertisement -