Post reply

Inserting multiple rows with select

  • July 22, 2009 at 4:01 am

    #136383

    Hi I hope someone can help me with the following problem.

    I have a table with records in it, the primary key is made up of 1 field and is numeric.

    i want to write an insert statement that will duplicate certain records.

    My insert statement looks like this

    Insert into table1 (Field1,Field2,Field3)

    Select maxnum(Field1),@NewNum,Field3

    from table1

    where field2 = 10

    maxnum is a function that gets the next incremental number.

    With one record copied this works but with more than one it fails. This is because when the select takes place the maxnum value for field1 always returns the same value for all returned rows as the insert has not taken place.

    is there a way to increment field1 in the select statement so that it is unique for each insert?

    Thanks for your help

  • July 22, 2009 at 7:06 am

    #1028279

    How about using a cursor?

    Eli

  • July 22, 2009 at 7:20 am

    #1028293

    forget the cursor, use a the row_number function or a Tally table to join to to get your starting max number:

    something like this, if the query returns multiple rows, would give you your PK value you need...but here's the kicker: why is the PK NOT an identity() column, if you are doing the same function manually to get a value and add one to it?

    changing that would resolve this problem instantly, and is a much better solution than finding the max and adding to it.

    Select maxnum(Field1) + row_number() over (ORDER BY FIELD3) ,@NewNum,Field3

    from table1

    where field2 = 10

    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!

  • July 22, 2009 at 7:23 am

    #1028297

    for reference i didnt want to use the cursor as its inefficient.

    row_number works a treat many thanks for the feedback

  • July 22, 2009 at 7:42 am

    #1028313

    Good call - should've thought of row_number.

    Would this still be off by 1? If the maxnum function retrieved the next incremental number and row_number starts at 1, then there will be a gap of 1 for the first record. You may need to deduct 1.

    Eli

  • July 22, 2009 at 7:51 am

    #1028319

    you are right...it looks like his maxnum() function returns the next available value...so it should be one less than that when added to row_number, unless he doesn't care about skipping a number....best to do as you identified: maxnum + rownumber -1

    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!

Viewing 6 posts - 1 through 5 (of 5 total)

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