"Jeff Kish" <jeff.kish@mro.com> wrote in message
news:b5a7u1lod76n3at35csf2b6f8nvpj35jn8@4ax.com...
> Hi.
> I'm a casual sql user. I have found a situation where I need to convert an
> oracle statement to tsql, one I can just fire off in any sql tool against
> an
> ms sql server database.
>
> I studied the exists statement and I think I understand it somewhat,
> however I
> was not sure how to get it quite right. If you have an idea and a minute
> or
> two I'd appreciate any insight or tutorial.
>
> insert into authorization (program, optiontitle, usergroup,
> authorizationid)
> select 'EVERYWHERE','NAVIGATOR',usergroup, authorizationseq.nextval
> from allgroups where exists (select * from authorization
> where authorization.USERGROUP = allgroups.USERGROUP and
> authorization.optiontitle = 'READ' and authorization.program =
> 'EVERYWHERE')
>
>
>
> I believe that because in my data, three values of usergroup from
> allgroups
> return true from the exists, that this is supposed to insert three rows
> into
> authorization.
>
> But I can't figure out what to do about the authorization.nextval.. I
> tried
> various max(authorization)+1
> etc but nothing seemed to compile/work
>
> thanks
> Jeff Kish

Jeff Kish wrote:

> 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

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

>
>Assuming SQL Server 2005 (you didn't specify otherwise), use the

>ROW_NUMBER function. For example:
>
>INSERT INTO [authorization]
> (program, optiontitle, usergroup, authorizationid)
> SELECT program, optiontitle, usergroup,
> authorizationid+
> (SELECT MAX(authorizationid)
> FROM [authorization])
> FROM
> (SELECT 'proq','titleq', usergroup, ROW_NUMBER()
> OVER (ORDER BY usergroup)
> FROM allgroups)
> AS T(program, optiontitle, usergroup, authorizationid) ;
>
>--
>David Portas, SQL Server MVP
>
>Whenever possible please post enough code to reproduce your problem.
>Including CREATE TABLE and INSERT statements usually helps.
>State what version of SQL Server you are using and specify the content
>of any error messages.
>
>SQL Server Books Online: