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 but if
have to use VBA, will use. Thanks

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200912/1