Post reply

Re: Exclusively acquiring newly insert id before insertion

  • August 28, 2008 at 8:32 am

    #211900

    Is there anyway to the next to-be-inserted identity value before actually inserting the value? I need to deal with multiple concurrent processes executing the following code:

    DECLARE @depth_log_id INT

    INSERT INTO crawler_depth (fk_batch_id, fk_crawler_link_id, hierarchy, depth)

    VALUES (@batch_id, @crawler_link_id, @base_hierarchy, @depth)

    SET @depth_log_id = @@IDENTITY

    UPDATE crawler_depth SET hierarchy = hierarchy + CAST(@depth_log_id AS VARCHAR(100)) + '.' WHERE pk_crawler_depth_log_id = @depth_log_id

    I was wondering if there's anyway to eliminate the UPDATE statement and use a single INSERT INTO statement.

  • August 28, 2008 at 8:38 am

    #864453

    First you should use @@scope_identity for the value.

    A trigger will get this if you need it always done. If not, moving to GUIDs allows you to generate things in advance.

  • August 28, 2008 at 8:44 am

    #864464

    Steve,

    Thanks for the suggestion about using @@scope_identity.

    How would I use a trigger to acquire the id before insertion? Won't there be problems because I need to use the to-be-inserted id to set the hierarchy table value?

    Thanks for your help.

  • August 29, 2008 at 2:02 am

    #864870

    Something similar to this

    CREATE TRIGGER dbo.trgMyTrigger ON Crawler_Depth

    AFTER INSERT

    AS

    SET NOCOUNT ON

    UPDATEcd

    SETcd.Hierarchy = cd.Hierarchy + CAST(cd.pkColumnNameHere AS VARCHAR(100)) + '.'

    FROMCrawler_Depth AS cd

    INNER JOINinserted AS i ON i.pkColumnNameHere = cd.pkColumnNameHere

    N 56°04'39.16"
    E 12°55'05.25"

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

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