Post reply

Is there any property to know whether the last insert was successful

  • April 29, 2010 at 3:47 am

    #224009

    Unlike @@IDENTITY, is there any property to know whether the last inserted identity column value.

    I mean if success then the value, else NULL value.

    Thanks,

    Ravi.

  • April 29, 2010 at 4:13 am

    #1159520

    @@Error will show you the error code of the previous statement. If the previous statement failed, it will be non-zero.

    You can also use the TRY .. CATCH syntax (available from SQL 2005 onwards). Something (roughly) like this.

    BEGIN TRY

    INSERT INTO SomeTable ...

    END TRY

    BEGIN CATCH

    -- The insert threw an error. Take appropriate action

    END CATCH

    You can use a number of error-related functions in the CATCH block, including ERROR_NUMBER(), ERROR_MESSAGE() and others. See Books Online for full detail.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • April 29, 2010 at 4:36 am

    #1159535

    Another option is to use the OUTPUT clause:

    SET XACT_ABORT OFF;

    DECLARE @test-2

    TABLE (

    row_id INTEGER IDENTITY PRIMARY KEY,

    data CHAR(1) NOT NULL

    );

    DECLARE @Output TABLE (row_id INTEGER NULL, data CHAR(1) NULL);

    INSERT @test-2 (data) OUTPUT inserted.row_id, inserted.data INTO @Output (row_id, data) VALUES ('A');

    INSERT @test-2 (data) OUTPUT inserted.row_id, inserted.data INTO @Output (row_id, data) VALUES (NULL);

    INSERT @test-2 (data) OUTPUT inserted.row_id, inserted.data INTO @Output (row_id, data) VALUES ('XX');

    INSERT @test-2 (data) OUTPUT inserted.row_id, inserted.data INTO @Output (row_id, data) VALUES ('Z');

    SELECT row_id, data FROM @Output;

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • May 7, 2010 at 2:31 am

    #1163872

    Thank you.

Viewing 4 posts - 1 through 3 (of 3 total)

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