Developer Forum

by **RXZl** » Thu, 28 Apr 2005 17:38:04

I have a table with 2 identity columns:
ID int identity(200100,1) not null,
OrderNo int Identity(550000,1) not null,
OrderName varchar(60) not null,
etc.

How do I get the values set by SQL server for ID and OrderNo when I insert
a new row in the table ?

Thanks in advance,

by **Uri Diman** » Thu, 28 Apr 2005 17:47:52

Eve
If you are sitting on SQL Server 2000 then perform after inserting
SELECT SCOPE_IDENTITY()

Otherwise SELECT @@IDENTITY

insert

by **TWFs** » Thu, 28 Apr 2005 17:54:02

It seems that there is a relationship between the two numbers.Seeing as you
can't have two identity columns. Why don't you use a formula for the orderno

Alter table <table> add OrderNo As ID + 300000.

by **TWFs** » Thu, 28 Apr 2005 17:54:08

And on another note. I can't see why you need and ID since the OrderNo is
already unique...

Why is this ?

by **RGF2aWQgUG** » Thu, 28 Apr 2005 18:04:03

I think you are mistaken. Only one IDENTITY column per table is permitted.
Please post the CREATE TABLE statement for your table so that we can
understand what you mean.

In SQL Server 2000 use SCOPE_IDENTITY() to retrieve the last inserted
IDENTITY value.

--
David Portas
SQL Server MVP

Developer Forum

Retrieve Identity on insert: what if table has 2 identity columns

Retrieve Identity on insert: what if table has 2 identity columns

Retrieve Identity on insert: what if table has 2 identity columns

Retrieve Identity on insert: what if table has 2 identity columns

Retrieve Identity on insert: what if table has 2 identity columns

Retrieve Identity on insert: what if table has 2 identity columns