How to supply next value to the surrogate Key inSQL Server

[sqlskrathan]

Hi,

I like to know how to populate a sequence number for the surrogate key in MS SQL Server. I have tried with the KeyMgtGetNextValue and @INROWNUM but both didn't work correctly for surrogate key. Do we have a sequencer in MS SQL Server so that we can start the sequence and increment that value with 1 by using a trigger.

I am using DataStage7 and MS SQL Server 2000.

Shashi

 

[redpine50]

You can generate sequence using identity function.

ex1)
CREATE TABLE new_employees
(
id_num int IDENTITY(1,1),
fname varchar (20),
minit char(1),
lname varchar(30)
)

ex2)
SELECT emp_id AS emp_num,
fname AS first,
minit AS middle,
lname AS last,
IDENTITY(smallint, 100, 1) AS job_num,
job_lvl AS job_level,
pub_id,
hire_date
INTO employees
FROM employee

for more information, see MS SQL server `Books Online'.