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 KishHi
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...equivalents.htm
http://www.microsoft.com/technet/pr...art2/c0761.mspx
John
"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,
authorizationseq seems to be a sequence. Sequences just don't exist in
MS SQL Server 2000/7/6.5. You might want to have an identity column.|||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...equivalents.htm
>http://www.microsoft.com/technet/pr...art2/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|||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
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:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||<snip>
>Assuming SQL Server 2005 (you didn't specify otherwise), use the
Sql Server 2000. Really sorry I did not say it up front. Do you know
of a way using that?
Regards,
Jeff
>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:
>http://msdn2.microsoft.com/library/...US,SQL.90).aspx|||Jeff Kish wrote:
> <snip>
> >Assuming SQL Server 2005 (you didn't specify otherwise), use the
> Sql Server 2000. Really sorry I did not say it up front. Do you know
> of a way using that?
> Regards,
> Jeff
INSERT INTO [authorization]
(program, optiontitle, usergroup, authorizationid)
SELECT program, optiontitle, usergroup,
authorizationid+
(SELECT COALESCE(MAX(authorizationid),0)
FROM [authorization])
FROM
(SELECT 'proq','titleq', A1.usergroup, COUNT(*)
FROM allgroups AS A1
JOIN allgroups AS A2
ON A1.usergroup >= A2.usergroup
GROUP BY A1.usergroup)
AS T(program, optiontitle, usergroup, authorizationid) ;
This assumes that usergroup is unique in Allgroups. If I'm wrong then
use the key of that table in the join. You need a key in order to
generate the sequence.
Read my signature. It may help you get faster answers in future.
Hope this helps.
--
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:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--
No comments:
Post a Comment