sequence

[Tomas Mudrak]

Hi,

I need little help with Ms SQL Server 2000. I would like to know how to
create sequence or something like that. I want to have an automatic counter
for each row in one entity, so then I can do something like this:

INSERT INTO table VALUES (use sequence(someth ing), value, value, .... )

Can you please help me?
thx
Tomas

[Dan Guzman]

You can use IDENTITY to assign an incremental value to a column. Example
below. The last identity value inserted on the current connection can be
retrieved with SCOPE_IDENTITY( ) or @@IDENTITY. Unlike @@IDENTITY,
SCOPE_IDENTITY( ) is not affected by triggers. See the Books Online for more
information.

CREATE TABLE MyTable
(
MyKey int NOT NULL IDENTITY(1, 1)
CONSTRAINT PK_MyTable PRIMARY KEY,
MyData varchar(10) NOT NULL
)

INSERT INTO MyTable (MyData) VALUES('A')
SELECT 'MyKey value is ' +
CAST(SCOPE_IDEN TITY() AS varchar(10))
INSERT INTO MyTable (MyData) VALUES('B')
SELECT 'MyKey value is ' +
CAST(SCOPE_IDEN TITY() AS varchar(10))
SELECT * FROM MyTable

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Tomas Mudrak" <60***@mail.mun i.cz> wrote in message
news:Hp******** @news.muni.cz.. .

Hi,

I need little help with Ms SQL Server 2000. I would like to know how to
create sequence or something like that. I want to have an automatic counter for each row in one entity, so then I can do something like this:

INSERT INTO table VALUES (use sequence(someth ing), value, value, .... )

Can you please help me?
thx
Tomas