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

    Unanswered: 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
    Yossi

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Could you set your ID attribute to an IDENTITY and let the system sort it out?
    Paul Young
    (Knowledge is power! Get some!)

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

  4. #4
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Okay, just wanted to rule out the obveous...

    How about something like:
    Code:
    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.
    Paul Young
    (Knowledge is power! Get some!)

  5. #5
    Join Date
    Aug 2002
    Posts
    46
    Originally posted by Paul Young
    Okay, just wanted to rule out the obveous...

    How about something like:
    Code:
    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.

  6. #6
    Join Date
    Aug 2002
    Posts
    46
    Originally posted by Paul Young
    Okay, just wanted to rule out the obveous...

    How about something like:
    Code:
    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

  7. #7
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    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)
    Paul Young
    (Knowledge is power! Get some!)

  8. #8
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    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.
    Paul Young
    (Knowledge is power! Get some!)

  9. #9
    Join Date
    Aug 2002
    Posts
    46
    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

  10. #10
    Join Date
    Feb 2002
    Posts
    2,232
    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.

Posting Permissions

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