Hi,
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)
Select LanTable.ProductNum,LanTable.LabelType
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.

Paul sugested the following:

Declare @Product_Num varchar(60)
Declare @LabelType varchar(60)

BEGIN

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)
begin
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
end
END

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.

Please advise what best to do.

Thanks
Yossi

You can re-seed the Identity, which means it will start generating new id's with the new "seed" that you specify.

Thanks alot mate.
i used DBCC CHECKIDENT (Label, RESEED, 1) and it works great.
cheers

I have another small qestion.

Can i set a column to be temporarly Identity and after using the Identity, to set the coloumn not to be Identity type?

Thanks again

Yes, using for instance Enterprise Mgr och through T-SQL (Alter Table).

I could'nt understand really.
How can i use Alter Table to change the field type?

Try this:

SET IDENTITY_INSERT DB_NAME ON

--Do the INSERT

SET IDENTITY_INSERT DB_NAME OFF

This only works on one table in a session at a time and allows you to insert any value into an IDENTITY column (assuming it's of the correct data type).

Does that help?

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.

Yes,
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

No, it keeps its IDENTITY characteristics when IDENTITY_INSERT is set OFF.

What happens is that when IDENTITY_INSERT is set to ON SQL Server no longer automatically updates the column, but still remembers it as an IDENTITY for other sessions and users.

Consider the following table:

CREATE TABLE DBFORUM (
ID INT IDENTITY,
UserName VARCHAR(50) NOT NULL,
)

Normally you can do the following INSERT:

INSERT INTO DBFORUM (UserName) VALUES('mattmoodie')

and SQL Server will add the next ID for you. However, with IDENTITY_INSERT set to ON, you must explicitly add the IDENTITY value:

INSERT INTO DBFORUM (ID, UserName) VALUES('123', 'mattmoodie')

Set IDENTITY_INSERT back to OFF and we can do it the old-fashioned way:

INSERT INTO DBFORUM (UserName) VALUES('garfild')

and SQL Server automatically adds the value, counting from the original mark, assuming you haven't added a higher value in your query. In that case, the new high value is used.

I understand now (finally).
Do you have an answer for the non identity field?
Thanks

No, I'm afraid I've never come across that situation. Sorry. I'll investigate a bit though.