Post reply

Identity on a Insert Select

  • May 3, 2004 at 8:21 am

    #163558

    Is there a way to do a select insert and get the identity for each record that is inserted? 

    for example if I do a:

    INSERT INTO @t (fieldB) SELECT afIEldhERE from @T1

    If @t has a field called FieldA that is an autonumber, is there a way to update @t1 with these values? 

    Thanks,

  • May 3, 2004 at 9:25 am

    #505015

    while if you are using a SP then use

    then wrap this in a transaction and run

    BEGIN TRAN

    INSERT INTO @t (fieldB) SELECT afIEldhERE from @T1

    update @t1 set filed = (select @@identity)

    COMMIT TRAN

     

    Thanks

    Prasad Bhogadi
    www.inforaise.com

  • May 3, 2004 at 10:17 am

    #505019

    I am a little confused by your answer.  Would this use a cursor to loop through all of the values to insert and insert them one at a time?  If yes, is there a way to do this without using a cursor? 

    Also, if I am reading the code correctly, then, This would stick the last records identity into all values for the @t1 table.  Example if you inserted 3 records then the id field for @t1 would all read 3.

  • May 3, 2004 at 3:51 pm

    #505046

    Ummm I think what you want would be something like this...

    DECLARE @iMin int

        , @iMax int

    DECLARE @t TABLE

        (

        intID    int    identity(1,1)

        , value nvarchar(255)

        )

    DECLARE @t2 TABLE

        (

        intID    int

        , value nvarchar(255)

        )

    INSERT INTO @t(value) VALUES ('first')

    INSERT INTO @t(value) VALUES ('second')

    -- Get the maxid for the later insert.

    SELECT @iMin = MAX(intID)

    FROM @t

    INSERT INTO @t(value) VALUES ('third')

    INSERT INTO @t(value) VALUES ('fourth')

    INSERT INTO @t(value) VALUES ('fifth')

    SET @iMax = scope_identity()

    INSERT INTO @t2(intID, value)

    SELECT intID

        , value

    FROM @t

    WHERE intID BETWEEN @iMin AND @iMax

    SELECT * FROM @t

    SELECT * FROM @t2

     




    Gary Johnson
    Microsoft Natural Language Group
    DBA, Sr. DB Engineer

    This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

  • May 3, 2004 at 10:04 pm

    #505065

    I think the same could be done using trigger on insert on said table @t. Within the trigger, you may please use the following to return all the identity values:

    select FieldA from Inserted

    I hope this way you would be able to retrieve all identity values inserted during the last insert using a select statement.

    Regards

    Amit


    Regards,
    Amit Khan

    Ontrack Systems Limited
    276B Lake Gardens
    Kolkata - 700045
    India
    Phone - 91-33-24178434,35
    Fax - 91-33-24221274
    Mobile - 91-33-9830105090

  • May 4, 2004 at 7:27 am

    #505130

    thanks for the post.  I guess it might help if I explained a little more, because maybe i am going about this all wrong.  I have an vb.net object that contains dates, times, and 0toN participants.  I am working on a disconnected model in which the user can create a new hearing object and not write it to the database until they save the entire record.  (hearing object is one small part of the bigger record). 

    The participants records are keyed off of the hearing key, which I do not get until after I have inserted the record.  I could use the post where i rematch all of the records after insert, but there might be identical hearings.  (I work with crazy people).   That is why I was looking for a @@identity or scope_identity() function that would work on sets.  Am I just totally off base with this?  If at all possible, I would like to not use a cursor. 

    Any help is greatly appreciated.

     

  • May 4, 2004 at 12:43 pm

    #505176

    David,

    I would probably still use the approach I posted. I would just use table variables or temp tables as an interim step before doing the actual insert into the main tables. I'm assuming of course that you are going to be using stored procedures to do the inserting of the data that are called from the VB.Net application.




    Gary Johnson
    Microsoft Natural Language Group
    DBA, Sr. DB Engineer

    This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

  • May 4, 2004 at 1:24 pm

    #505185

    yes, the system is using stored procedures.  I am using 2 varchar(8000) to pass in xml strings produced by a dataset.  I will try the temp table and see what I can get to work.  BTW Gary, is the URL in your "AS IS" disclaimer needing an "o" in "copyright.htm"?

    Thanks for the help.

  • May 4, 2004 at 6:42 pm

    #505222

    Actually I'm removing the URL. After re-reading it the page is more geared for me than you Oh well. As an MS Employee I've been asked to put the disclaimer on my posts. So I was trying to do the right thing. That page is way overkill for the small scripts I post on this board. Thanks for making me re-read it!




    Gary Johnson
    Microsoft Natural Language Group
    DBA, Sr. DB Engineer

    This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

  • May 5, 2004 at 8:57 am

    #505319

    no problem, glad I could help.

Viewing 10 posts - 1 through 9 (of 9 total)

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