Post reply

How COMMIT works in SQL Server?

  • July 30, 2009 at 5:51 pm

    #140346

    Hi,

    I want make sure how the COMMIT statement works in SQL Server.

    In ORACLE, We can see the no.of records updated using Set serveroutput on & DBMS_OUTPUT.PUT_LINE and if the no.of updated rows matches, then can issue COMMIT statement & if not ROLLBACK.

    Now in SQL Server, I have issued below command and it immediately reflected the chage by giving "no.of rows effected 1"

    UPDATE [Mydb].[dbo].[table1]

    SET [col1] = 'USA' where col1 = 'UK'

    Here, I want to see how many records its going to update and then I want issue COMMIT statement. How can we achive this in SQL Server?

  • July 30, 2009 at 8:07 pm

    #1032764

    you need to explicitly start a transaction, because SQL server assumes by default that every command is auto-committed unless there is a BEGIN TRAN command;but other than that it is very similar:

    to get the number of rows affected, there is a @@rowcount variable you can query.

    BEGIN TRAN

    declare @myrowcount int

    UPDATE [Mydb].[dbo].[table1]

    SET [col1] = 'USA' where col1 = 'UK'

    SELECT @myrowcount = @@rowcount --this tells you how many rows were affected by the last command

    if @myrowcount = 0

    COMMIT TRAN

    ELSE

    ROLLBACK TRAN

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply