Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2003
    Posts
    19

    Unhappy Unanswered: processing each row in a multirow insert

    I'm looking for a way to process each row in a insert ... select sentece. What i need is to update a column which is type int (not identity nor sequence) in a way it always get the max record + 1, in a sentence:

    insert into my_table
    (a,b,c,d)
    select isnull((select max(id) from another_table),0) as a,
    'b' as b, 'c' as c, 'd' as d
    from table1 join table2 on t1=t2

    the problem with such a sentence is the embbebed select is it is executed only once so it provides always the same value, i.e. assuming the subselect returns 7 and the join provides 2 rows, what would be inserted in my_table is:

    a b c d
    -- -- -- --
    7 b c d
    7 b c d

    and not

    7 b c d
    8 b c d

    what i expected

    so what i looking for is a way to resolve such a sentence or at least some kind of fuction which generates a identity value in a similar way as identity() but being useful for no-identity tables. Maybe something similar to newid() but returning int value and it would be great if that value would be max(table_field)+1

    any idea?

  2. #2
    Join Date
    Dec 2003
    Posts
    19

    Re: processing each row in a multirow insert

    i forgot to say don't want to use a cursor to process and insert each row

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    This can be done using a single direct, if complicated, SQL statement. It would be simpler to insert your data into a temporary table with an autoincrement field, and then select from the temporary table, adding max(id) to the values in the autoincrement field.

    Otherwise, you will need a select statement with a subquery that fabricates sequential values for your data (what order to you want them in? You have to choose an order with this method) and then add max(id) to these sequential values.

    blindman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •