quote:

---

Originally posted by lpatters

Hello,

I have a table that contains columns Type_Business_Id and Type_Business. Whenever a new type of business is defined and needs to be added to the table I need to be able to search through the table and find any unused Ids and assign the unused Id to the new type. I know I need to do some kind of looping, but I can't seem to figure out exactly what I need to do. Any help would be appreciated.


LPatters

---

quote:

---

Originally posted by jhocutt

create table #tmp (
	Type_Business_Id int, 
	Type_Business varchar(20)
)

insert into #tmp ( Type_Business_Id, Type_Business )
select 1, 'Test1' union all
select 3, 'Test3' union all
select 4, 'Test4' union all
select 5, 'Test5' union all
select 7, 'Test7' union all
select 8, 'Test8' union all
select 9, 'Test9'

select Min(t1.Type_Business_Id+1) as Lowest_Available_Int
from #tmp t1 left join #tmp t2 on t1.Type_Business_Id+1 = t2.Type_Business_Id
where t2.Type_Business_Id is NULL


select t1.Type_Business_Id+1 as ALL_Available_Ints
from #tmp t1 left join #tmp t2 on t1.Type_Business_Id+1 = t2.Type_Business_Id
where t2.Type_Business_Id is NULL

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

---

quote:

---

Originally posted by visakh16

quote:

---

Originally posted by lpatters

Hello,

I have a table that contains columns Type_Business_Id and Type_Business. Whenever a new type of business is defined and needs to be added to the table I need to be able to search through the table and find any unused Ids and assign the unused Id to the new type. I know I need to do some kind of looping, but I can't seem to figure out exactly what I need to do. Any help would be appreciated.


LPatters

---

why do you want to do this way? all you need to do is to declare Type_Business_Id as identity column so that it automatically gives you next id value and you dont have to worry too much for its generation. Also i wont personally be too much concerned about itermediate gaps caused by adhoc deletes of records.

---

quote:

---

Originally posted by lpatters
My senior DBA prefers to do it this way because we have a lot of unused ids. Instead of assigning new ids he wants to use up all the extras in the table.

---