Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Server 2000 creating sequence and equivalent of currval and nextval

Posted on 2004-09-28
4
Medium Priority
?
1,876 Views
Last Modified: 2007-12-19
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
0
Comment
Question by:dylanone
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 15

Accepted Solution

by:
jdlambert1 earned 800 total points
ID: 12176101
SQL Server doesn't use sequences like Oracle does. Here's how to handle it.

Define the ID column in the Students table as having the IDENTITY value. If you're using Enterprise Manager to create the table, the Identity option is at the bottom of the window. If you're using SQL, it's like this:

CREATE TABLE Students(ID as IDENTITY(1,1), First_Name varchar(50), Last_Name varchar(50))

Now you can do an insert while ignoring the ID column, and the value will be incremented and inserted automatically:
INSERT Students(First_Name, Last_Name) VALUES('Scott','Smith')

However since you want to use the new ID in second insert, the easiest way is by creating a stored procedure to do both inserts:

CREATE PROCEDURE up_InsertStudent @FirstName varchar(50), @LastName varchar(50), @Grade tinyint AS

BEGIN TRAN
  INSERT students(id, first_name, last_name) VALUES (@FirstName, @LastName)
  INSERT other_students(id, last_name, grade) VALUES(@@Identity, @LastName, @Grade)
COMMIT


You use this stored procedure like this:
EXECUTE up_InsertStudent 'Scott', 'Smith', 95
0
 

Author Comment

by:dylanone
ID: 12179523
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?

0
 

Author Comment

by:dylanone
ID: 12184223
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:

http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21150107.html

0
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12184379
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...
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question