Results 1 to 12 of 12
  1. #1
    Join Date
    Aug 2002
    Posts
    46

    Unanswered: Add consecutive Id in Insert mode - followup

    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

  2. #2
    Join Date
    Jul 2002
    Posts
    229
    You can re-seed the Identity, which means it will start generating new id's with the new "seed" that you specify.

  3. #3
    Join Date
    Aug 2002
    Posts
    46
    Originally posted by Coolberg
    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

  4. #4
    Join Date
    Aug 2002
    Posts
    46
    Originally posted by Coolberg
    You can re-seed the Identity, which means it will start generating new id's with the new "seed" that you specify.
    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

  5. #5
    Join Date
    Jul 2002
    Posts
    229
    Yes, using for instance Enterprise Mgr och through T-SQL (Alter Table).

  6. #6
    Join Date
    Aug 2002
    Posts
    46
    Originally posted by Coolberg
    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?

  7. #7
    Join Date
    Nov 2002
    Location
    Glasgow, Scotland
    Posts
    5

    IDENTITY_INSERT

    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?

  8. #8
    Join Date
    Nov 2002
    Location
    Glasgow, Scotland
    Posts
    5

    Of course...

    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.

  9. #9
    Join Date
    Aug 2002
    Posts
    46

    Re: Of course...

    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.
    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

  10. #10
    Join Date
    Nov 2002
    Location
    Glasgow, Scotland
    Posts
    5

    Re: Of course...

    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.

  11. #11
    Join Date
    Aug 2002
    Posts
    46

    Re: Of course...

    Originally posted by mattmoodie
    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

  12. #12
    Join Date
    Nov 2002
    Location
    Glasgow, Scotland
    Posts
    5
    No, I'm afraid I've never come across that situation. Sorry. I'll investigate a bit though.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •