Post reply

Getting all identity values in a batch insert statement

  • July 8, 2009 at 2:03 pm

    #384942

    Hi there,

    I have a table that has an identiy column as the primary key. My question is how do I get/retrieve all the identity values from my batch insert statement. I need this because we have a header-detail tables. I need all the header identity values as a foreign key when I do the batch insert for my detail table. I have to use t-sql and not store procedure or trigger. Also, it would be nice if it works for both sql 2000/2005 because we both support/use these product. Another thing is I'm using a java jdbc driver to execute this batch statements.

    Your help is very much appreciated.

  • July 8, 2009 at 2:12 pm

    #1021907

    It would help to have the DDL (create table statements), sample data (in a readily consummable format that can be cut/paste/run in SSMS) in order to help you better.

    Please read the first article I reference below in my signature block for more on how to post htis information for better help.

  • July 15, 2009 at 4:37 pm

    #1025407

    Do you have another (natural) key on the data you are bulk-inserting? If yes you could just re-select the ID column for those keys. That's of course assuming that the natural key is unique (but you say you are inserting, so I guess that would be the case) and that the second round-trip to the server is not too expensive.

    One thing the guys on one of our .NET projects did is to use a GUID as the PK instead of an IDENTITY column, pre-generate those on the client side and populate the FK's in the child tables with those, then submit the whole dataset in one go. Saved them the roundtrip which was important coz of bandwidth (= cost) issues on this side of the world. I'm sure there's a way to do this in the Java world as well (am not a Java programmer). Using GUID's as PK's has its own caveats, but that's another issue.

    But as Lynn pointed out, in order to give more specific and to the point advise, it would be very handy if you were to supply us with DDL statements for the tables involved, some sample data of what's already in the target tables as well as samples of what you are inserting and what the desired outcomes should be.

    Hope this helped a bit,

    Jan

    EDIT: Slightly reworded. Two "issues" in one sentence is one too many 😉

    --------------------------------------------------------------------------
    A little knowledge is a dangerous thing (Alexander Pope)
    In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)[/url]

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

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