Posted by Jeff Kish on 02/06/06 19:59

On Sat, 4 Feb 2006 16:17:55 -0000, "John Bell" <jbellnewsposts@hotmail.com>
wrote:

>Hi
>
>You can make the column an identity, this will not guarantee contiguous
>number but it will be increasing/decreasing and unique. You can then miss it
>out from the statement altogether.
>
>These may help:
>http://vyaskn.tripod.com/oracle_sql_server_differences_equivalents.htm
>http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit//part2/c0761.mspx
thanks. I'm still not sure of how to do something here, though.

This is directly related to the problem but re-worded because I need to
get the next value using max(authorizationid)+1 ...

Given two tables:
allgroups(usergroup, otherdata) =
{'group1',otherdata1,
'group2',otherdata2,
'group3',otherdata3,
:
:
'groupn',otherdatan}

and
authorization(program,optiontitle,
usergroup,authorizationid) =
{'pro1','title1','ug1',3,
'pro2','title2','ug2',4,
:
'pron','titlen','ugn',m}

How can I insert multiple
lines (one for each usergroup
in allgroups) using one sql statement
into authorization if this is correct for a
single insert:
insert into authorization(program,
optiontitle,usergroup,
authorizationid)
select 'proq','titleq','ug1',
max(authorizationid)+1
from authorization

bascially I'd like each usergroup
from allgroups to be used to create a
new line in authorization, having
the authorizationid increment one from
the current max.

Yes, I have no control over the design/use of
an identity column.

Is it possible?
Thanks
Jeff Kish

[Back to original message]