Auto generating column value with respect to another column

Question

Hi,

In my application when i insert data i have to generate sequential value for a particular column basing on value in other column. Is there any property which i could set in SQL Server 2005.

I am explaining my requirement in more detail:

I have table with two fields id and branchid, i have to insert sequential value for branchid automatically for every unique value of id

That is

id   -  BranchId
1    -    1,
1    -    2,
1    -    3,   

Now if i insert a row with id value 2, then branchid should automatically set with value 1

Thanks and Regards Suraj.

Answer 1

I do not know of any field value / property you could set.

One possible solution: - Put a trigger on Inserts that will create the correct BranchId for you

Answer 2

You dont need a second column. Just use row_number() function with partition by clause

declare @t table(id int)
insert into @t
select 1 union all
select 1 union all
select 1 union all
select 2
select id,row_number() over (partition by id order by id) as Brandid from @t

Answer 3

Use Formula Fields and if you logic is clear then create the Function for that use that function in Second column in Formula Fields and it automatically set the value for that.

Answer 4

Well, Madhivanan's approach will work when you SELECT data and I appreciate that as that will also reduce storage space. Here is just a way if want to keep the generated BrandId in your table.

You just need to perform an UPDATE after you INSET values to Id column.

--Table with sample data
DECLARE @Tbl TABLE(ID INT, BRANDID INT)
INSERT INTO @Tbl(ID) VALUES (1),(1),(1),(2)

--extract data
SELECT * FROM @Tbl


--Populate BrandId with auto generated id
;WITH POPULATEBRANDID
AS
(
SELECT ID, BRANDID, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID) AS [myBRANDID]
FROM @Tbl
)
UPDATE POPULATEBRANDID
SET BRANDID=myBRANDID
WHERE BRANDID IS NULL

--extract data
SELECT * FROM @Tbl

Let me know if it works for you.