asked on

SQL Server 2000 creating sequence and equivalent of currval and nextval

Currently I have a need in a web interface I'm building in ASP to call a sequence via SQL for an insert statement. However, I have a strong Oracle background and don't know how to create a sequence in SQL Server 2000. Additionally I have a need for a second insert statement on the page to use the currval or same number as the first sequence.

I can explain this better if need be - but basically I'm trying to do the following in SQL Server 2000 like the Oracle equivalent of the following:

insert into students (id, first_name, last_name) values student_sequence.NEXTVAL, 'Scott', 'Smith');

then a second insert statement will place data into another table but use that same id's value:

insert into other_students(id, last_name, grade) values
students_sequence.CURRVAL, 'Smith, 'A')

Thus, I don't know how to create a sequence in SQL Server 2000 nor use the equivalent of nextval and currval

Thanks

ASKER CERTIFIED SOLUTION

jdlambert1

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dylanone

ASKER

The above solution looks - good I haven't tried to run it yet - since it appears the primary key columns of id are varchars and don't have an identity associated with them and I can't add them because they are generated via an outside process.

Therefore is still some other round about way - to generating some kind of unique sequence using SQL Server - they don't have any kind of built in function to do something like this that I can just call during my insert of those values?

Could I also possible create my own outside sequence table?

dylanone

ASKER

I closed this one out because the above did answer my question - I just posted a new question as I'm still fiddling around with the sequences:

https://www.experts-exchange.com/questions/21150107/SQl-Server-Sequence-Function-Getting-To-Increment.html

jdlambert1

Nuts. I composed a long response to your post from 9:26 -- but I must have gotten distracted before I hit submit. I'll try to check out your next Q when I get home tonight. Sorry...