Sql - Dual identity (autonumber) columns

[vatik]

I need a table with the following structure.

Id Item_Number Item_Description
1 3000 asdfasdfasdf
2 3001 asdfasdfasdf
3 3002 asdfasdfasdf
4 3003 asdfasdfasdf
5 3004 asdfasdfasdf
6 3005 asdfasdfasdf
7 3006 asdfasdfasdf

As you can see, the id field must be the identity field, but I want to autonumber the Item_Number Field sequentially, with the seed at 3000. Is this possible?

 

[Dimandja]

I am not sure you could do that. But, you can still implement it by using a trigger on "Item_Number".

 

[r937]

or use a computed column where Item_Number is defined as Id+2999

rudy

http://r937.com/

SQL Consulting

 

[jdgonzalez]

There are a few approaches you can take.
1. Create a trigger that will update the record you just inserted so that it will increment the number.
2. Use a stored procedure to insert data into the table and increment accordingly. See below.

create proc addItem
@item_desc varchar(200) = 'No description passed'
as
declare @item_number_before int
declare @item_number int
begin trans
  @item_number_before = select max(item_number)
                        from your_table
  @item_number = @item_number_before + 1

  insert into your_table
       (item_number, item_desc)
  values
       (@item_number, @item_desc)
commit trans

 

[SQLSister]

YOu cannot have two identity fields in one table. Just add 2999 to the value of the identity field and insert it into the other field. Make it a computer column rather than a trigger as this will be more efficient.

Questions about posting. See faq183-874

 

[r937]

that's what i said, only i said computed, not "computer"

i know, i know, it was just a tyop



rudy

http://r937.com/

SQL Consulting

 

[Dimandja]

If you use computed column, you should read this:

http://www.microsoft.com/sql/techinfo/tips/development/July2.asp

 

[SQLSister]

OK rudy, we all know I can't type.

Questions about posting. See faq183-874

 

[vatik]

Thanks for the replies everyone.

I ended up doing it with asp

I sorted the sql by one field and ordered by another in desc.

Then i made a variable that took the currentRs and incremented the value of its field by one. I also made an optional seed value for starting the numbering at a certain point (1000...1001...etc)

so i have out put such as

id field

1 1004
2 1005
5 1006
6 1007