Add consecutive Id in Insert mode

Hi,
I am using the following procedure to fill Product table from LanTable:

BEGIN
insert into Product (Product_Num,Sticker_type)
Select LanTable.ProductNum,LanTable.StickerType
From LanTable left Join Product p On LanTable.ProductNum=p.Product_Num where p.Product_Num is null
END

In Product table i have an additional ID column.

I need to fill this field with consecutive numbers according to the Insert above.
If current ID value is 10 and I have 20 new products to insert, the ID field will be filled with 11 to 31.
How can I insert into ID column consecutive numbers starting with 11 that dependes on the number of rows added to Product table?
Thanks
YossiCould you set your ID attribute to an IDENTITY and let the system sort it out?|||Originally posted by Paul Young
Could you set your ID attribute to an IDENTITY and let the system sort it out?
Thanks for your replay.
The Id column has a meaning.
Not every time I will use the Insert routine the Id should get the consecutive value. Thats why i need to know the current Id and from that value to work on. with Identity the values will raise up to the roof.
Yours
Yossi|||Okay, just wanted to rule out the obveous...

How about something like:
declare @.Product_Num int, @.Sticker_Type as int

select @.Product_Num = min(LanTable.ProductNum) From LanTable left Join Product p On LanTable.ProductNum=p.Product_Num where p.Product_Num is null
select @.Sticker_Type = Sticker_Type from LanTable where ProductNum = @.Product_Num
while (@.Product_Num is not null) begin
insert into Product (ID_Column,Product_Num,Sticker_type)
select max(ID_Column) + 1, @.Product_Num int, @.Sticker_Type from Product
select @.Product_Num = min(LanTable.ProductNum) From LanTable left Join Product p On LanTable.ProductNum=p.Product_Num where p.Product_Num is null and LanTable.ProductNum > @.Product_Num
select @.Sticker_Type = Sticker_Type from LanTable where ProductNum = @.Product_Num
end

Of course this is UNTESTED and you will need to change datatypes and attribute names, but look it over and let me know your thoughts.|||Originally posted by Paul Young
Okay, just wanted to rule out the obveous...

How about something like:
declare @.Product_Num int, @.Sticker_Type as int

select @.Product_Num = min(LanTable.ProductNum) From LanTable left Join Product p On LanTable.ProductNum=p.Product_Num where p.Product_Num is null
select @.Sticker_Type = Sticker_Type from LanTable where ProductNum = @.Product_Num
while (@.Product_Num is not null) begin
insert into Product (ID_Column,Product_Num,Sticker_type)
select max(ID_Column) + 1, @.Product_Num int, @.Sticker_Type from Product
select @.Product_Num = min(LanTable.ProductNum) From LanTable left Join Product p On LanTable.ProductNum=p.Product_Num where p.Product_Num is null and LanTable.ProductNum > @.Product_Num
select @.Sticker_Type = Sticker_Type from LanTable where ProductNum = @.Product_Num
end

Of course this is UNTESTED and you will need to change datatypes and attribute names, but look it over and let me know your thoughts.

I will work on it....

Thanks a bunch mate.|||Originally posted by Paul Young
Okay, just wanted to rule out the obveous...

How about something like:
declare @.Product_Num int, @.Sticker_Type as int

select @.Product_Num = min(LanTable.ProductNum) From LanTable left Join Product p On LanTable.ProductNum=p.Product_Num where p.Product_Num is null
select @.Sticker_Type = Sticker_Type from LanTable where ProductNum = @.Product_Num
while (@.Product_Num is not null) begin
insert into Product (ID_Column,Product_Num,Sticker_type)
select max(ID_Column) + 1, @.Product_Num int, @.Sticker_Type from Product
select @.Product_Num = min(LanTable.ProductNum) From LanTable left Join Product p On LanTable.ProductNum=p.Product_Num where p.Product_Num is null and LanTable.ProductNum > @.Product_Num
select @.Sticker_Type = Sticker_Type from LanTable where ProductNum = @.Product_Num
end

Of course this is UNTESTED and you will need to change datatypes and attribute names, but look it over and let me know your thoughts.
Hi Paul,
I have tried that and I have the following problems:
1.Product_Num is a barcode string, min function cannot help us here.
2.ID_Column can be with no value at first (when the table is empty there is no value) but it cannot be nulled because its the PK so I got an Error trying to draw the Max value. (i can deal with that).

The main problem is 1.
Can you help with that?
Thanks|||Sorry,

1. Yes, min will work with strings. You will need to adjust the local variable datatypes to match your table attributes. Of course if ProductNum is not unique this approach probably wont work well but then I suspect you will have other problems as well. if this is still giving you fits, post the ddl from the Product and LanTable tables.

2. The quick fix on the ID_Column is to test for a null i.e. isnull(max(ID_COLUMN) + 1,1)|||Again, I would encourage you to look at using an Identity Attribute as it will accomplish exactly what you are trying to create. Yes the value just keeps growing but so what. IMHO trying to reclaime gaps in IDs is a wast of time.

Assuming you want to persue the DIY approach, what will you insert into the Product.LabelId attribute untill the trigger can assign the correct ID? You can't leave it blank or insert a default value? PK suggests Not Null and unique.

Triggers should always be written to handle multiple rows, it only takes a little more effort.

If your primary key is NOT unique (bad idea) you can probably make the trigger approach work. The key is to process each record of the temporary inserted table one at a time. Basically take the code I provided earlier and modify it to start a transaction, update the FreeID table to the next value, select the next ID, end the transaction, process one record from the isnerted table and then repeat until all reacords are processed.

Digest all of this and let me know.|||Originally posted by Paul Young
Again, I would encourage you to look at using an Identity Attribute as it will accomplish exactly what you are trying to create. Yes the value just keeps growing but so what. IMHO trying to reclaime gaps in IDs is a wast of time.

Assuming you want to persue the DIY approach, what will you insert into the Product.LabelId attribute untill the trigger can assign the correct ID? You can't leave it blank or insert a default value? PK suggests Not Null and unique.

Triggers should always be written to handle multiple rows, it only takes a little more effort.

If your primary key is NOT unique (bad idea) you can probably make the trigger approach work. The key is to process each record of the temporary inserted table one at a time. Basically take the code I provided earlier and modify it to start a transaction, update the FreeID table to the next value, select the next ID, end the transaction, process one record from the isnerted table and then repeat until all reacords are processed.

Digest all of this and let me know.

I will do that.
cheers|||I am glad to see that you are following Paul's advice. While I was reading your post, the description was a primary key field that needed to be incremented - and I was curious why you said you did not want to use IDENTITY (I could only think of the gaps as Paul mentioned - as a down side). Anyway, you changed your mind - so good luck.