Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2005
    Posts
    28

    Unanswered: Append Query w/ 'next number'

    Hello Gurus,

    Scenario:
    I have an oracle database connected thru ODBC. I want to append data to Table-A that has following in it:

    Col1 (Integer), Col2 (text)
    -------------- ---------
    10 txt1
    12 txt2
    9 txt3

    I have a local table Table-B w/ the following Values:
    Col1 (text)
    ----------
    txt5
    txt6
    txt7

    The result I want in Table-A after appending from TAble-B is:

    Col1 (Integer), Col2 (text)
    -------------- ---------
    10 txt1
    12 txt2
    9 txt3
    13 txt5
    14 txt6
    15 txt7

    Notice the Col1 values 13, 14 and 15. What I want is to get the max of Col1 in existing table-A (which is 12) and then add 1 to that (that becomes 13). Then repeat the same for next row but this time the max should be 13. And so on.

    Following is the query I used:

    INSERT INTO Table_A ( Col1, Col2)
    SELECT (select max(C1.Col1) from Table-A C1) + 1, Table_B.Col2
    FROM Table_B;

    This gives me the following results:
    Col1 (Integer), Col2 (text)
    -------------- ---------
    10 txt1
    12 txt2
    9 txt3
    13 txt5
    13 txt6
    13 txt7

    Please help me achieve the result I want.

    Thanks atom in advance.

    Sunil

    PS: NB - I do not know VB etc so would prefer to have SQL level help. Thanks

  2. #2
    Join Date
    Apr 2005
    Posts
    28
    Someone - please help...

Posting Permissions

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