CODEWITHSUNDEEP
sql-server-2008identityidentity-column
Romano Zumbé
Romano Zumbé

Reputation: 8099

SQL IDENTITY column based on another column

Is there a way to define an identity column on another column? What I want to accomplish is a table that holds positions of an order and these orders can be put there anytime. So it could be that there are already lets say three positions in the table and it would look somewhat like this:

OrderNumber | OrderPosition
10001         1
10001         2
10001         3

And now I want to add another position without calculating the right value for the OrderPosition column. This is because I want to write new positions for multiple orders into the table and would like to avoid cursoring over the individual orders. I would prefer a solution wher OrderPosition is an identity column that is reseeded based on the OrderNumber column. So that If i add an order position for a new order it would start with 1 and if I add another position for order 10001 it would continue with 4.

Upvotes: 2

Views: 3728

Answers (2)

Ajay2707
Ajay2707

Reputation: 5808

your requirement will not work for identity column.

You need to create custom logic to get from the normal columns and on combination based new no will generate.. like (read comments, only choose one logic)

declare @t table(OrderNumber  int, OrderPosition int)

insert into @t values (10001, 1),(10001, 2),(10001, 3),(10001, 4)

select * from @t

--now insert new record with old orderno
declare @seq int = 1
declare @ordernumberNew int = 10001 

--Eigher you can use :- insert to more understand
if( exists(select orderposition from @t where OrderNumber = @ordernumberNew ))
 begin
    set @seq = (select max(OrderPosition) + 1 from @t where OrderNumber = @ordernumberNew )
 end

insert into @t values (@ordernumberNew , @seq )

select * from @t

--or another twist of above statement, insert directly as
    insert into @t
    values
    (
        @ordernumberNew,
        case when exists (select orderposition from @t where OrderNumber = @ordernumberNew )
        then (select max(OrderPosition) + 1  from @t where OrderNumber = @ordernumberNew )
        else 1 end
    )

select * from @t    

--Now enter the not exist order no
set @ordernumberNew = 10006

    insert into @t
    values
    (
        @ordernumberNew,
        case when exists (select orderposition from @t where OrderNumber = @ordernumberNew )
        then (select max(OrderPosition) + 1  from @t where OrderNumber = @ordernumberNew )
        else 1 end
    )

select * from @t

Upvotes: 0

Dbloch
Dbloch

Reputation: 2376

Write a Scalar Function that returns the MAX(OrderPosition) based on OrderNumber. Then reference that function in the insert statement of orders

Upvotes: 1

Related Questions