Post reply

capture PK of inserted records

  • March 31, 2011 at 12:07 pm

    #237067

    INSERT INTO dbo.tblAccount

    (

    AccountPK

    ...............

    )

    Select @AccountPK .........(other column values)..............

    FROM

    @TmpAcct

    Where ..............

    how can we capture new AccountPK inserted, stored or returned, for further procesing?

  • March 31, 2011 at 12:35 pm

    #1306157

    you want to use the OUTPUT Clause; it's especially helpful when you insert more than one row.

    from what I've read recently, even SCOPE_IDENTITY() function can fail to return correct values in certain situations (see SCOPE_IDENTITY() sometimes returns incorrect value ; the only thing i've heard that is bulletproof is the OUTPUT clause.

    here's a simple example:

    CREATE TABLE adds(

    adid INT IDENTITY(1,1) NOT NULL PRIMARY KEY,

    code VARCHAR(30) )

    DECLARE @MyResults TABLE(

    ID int,

    newcode VARCHAR(30),

    oldcode VARCHAR(30) )

    INSERT INTO adds(code)

    OUTPUT

    INSERTED.adid,

    INSERTED.code,

    NULL

    INTO @MyResults

    SELECT 'aliceblue' UNION ALL SELECT 'antiquewhite' UNION ALL

    SELECT 'aqua*' UNION ALL SELECT 'aqua*' UNION ALL

    SELECT 'aquamarine' UNION ALL SELECT 'azure' UNION ALL

    SELECT 'beige' UNION ALL SELECT 'bisque' UNION ALL

    SELECT 'black*' UNION ALL SELECT 'black*' UNION ALL

    SELECT 'blanchedalmond' UNION ALL SELECT 'blue*' UNION ALL

    SELECT 'blue*' UNION ALL SELECT 'blueviolet' UNION ALL

    SELECT 'brown' UNION ALL SELECT 'burlywood' UNION ALL

    SELECT 'cadetblue'

    --use the combined results for additional processing, like inserting into child tables that need that PK:

    SELECT * FROM @MyResults

    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!

  • April 1, 2011 at 7:46 am

    #1306480

    good code.

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

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