Unanswered: Add consecutive Id in Insert mode - followup
This is a buthering subject i raised up 2 days ago with the good help of Paul and rnealejr.
My problem is that i need to fill a table that has an LabelId column from another table.
If I will use IDENTITY for the LabelId column, each time i will clear the table and refill it, the LabelId will start with a new high value. The LabelId column does not represent just an index, it represent an LabelId of electronic label (our product) and we are limited to the numbers of Id's we can supply.
Its good to use IDENTITY but with the condition that after clearing the table and refilling it the starting number of LabelId would be set by me (generaly 1).
Can i do that?
using the following:
insert into Label (Product_Num,Label_Type)
From LanTable left Join Label p On LanTable.ProductNum=p.Product_Num where p.Product_Num is null
with LabelId using IDENTITY Id will reach the limit (Hex-FFFFF) very fast if I am clearing a table (from any reason) and insert all rows into the table again.
select @Product_Num = min(LanTable.ProductNum) From LanTable left Join Label p On LanTable.ProductNum=p.Product_Num where p.Product_Num is null
select @LabelType = LabelType From LanTable where ProductNum = @Product_Num
while (@Product_Num is not null)
insert into Label (LabelId,Product_Num, Label_Type)
SELECT isnull(max(LabelId) + 1,1),@Product_Num,@LabelType from Label
select @Product_Num = min(LanTable.ProductNum) From LanTable left Join Label p On LanTable.ProductNum=p.Product_Num where p.Product_Num is null and LanTable.ProductNum > @Product_Num
select @LabelType = LabelType from LanTable where ProductNum = @Product_Num
Its working but for thousends of rows its very slow.
The above Insert into command is fast but as i said i cannot set the starting value of LabelId and its critical for me.
Originally posted by mattmoodie
The above assumes you have an IDENTITY column that you wish to diasable temporarily, but it adds up to the same thing as enabling a column temporarily.
Does that mean that after setting IDENTITY_INSERT to Off, that this field is no longer Identity typed?
The thing is i preffer is possible to set the type not to Identity, In code to chage the type to Identity and them change back.
Is it possible ?
Thaks for the help mate