Post reply

How to Insert multiple rows if quantity is more than 1

  • January 25, 2009 at 8:28 am

    #103665

    Hi,

    I am developing an application. In database there are three tables one 'KitHeader' second 'kitDetails' and third is 'OrderKitItems'. Kit details contains Item and its quantity. Now while selecting a kit for an Order I insert kit items in OrderKitItems table. I want that in case of Quantity of an Item is more than 1 then OrderKitItems table should have multiple rows equal to quantity of that Item for the Item.

    Right now I am using cursor but is there any other way of doing this without using cursor?

  • January 25, 2009 at 10:14 am

    #932389

    Do you mean that if there is a quantity of 3 for the it items, you need to insert 3 rows into order details? Are these the same rows? What is difference between the rows?

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • January 25, 2009 at 10:28 am

    #932396

    Yes exactly same row for three times

  • January 26, 2009 at 2:52 am

    #932502

    May I ask why, exactly, you would be doing that rather than have a quantity column containing the number of items?

  • January 26, 2009 at 3:07 am

    #932506

    set @count = 0

    select quantity from .....

    while @count < quantity

    begin

    set @count = @count + 1

    insert .......

    end

  • January 26, 2009 at 3:40 am

    #932513

    Hi Andrew,

    Actually this is a client requirement. Client needs a facility to update status of the Item i.e. Faulty, Lost, Sold etc. for each item. So in case of one item is faulty and one is lost it is easier if each item is displayed in separate line.

  • January 26, 2009 at 3:54 am

    #932516

    Not a god requirement. How would the client distinguish them? Each part should have its serial number, so if the user does not enter (type, scan,...) each part, multiple lines have no sense.

    Besides, if you have many exactly same records, you have a problem updating exactly one of them.

  • January 26, 2009 at 4:53 am

    #932524

    There are two type of items one is Serialized like IMEI, SIM etc. and non serialized items like battery, charger etc.. Serialized item always going to be one in one kit but non serialized may be more than one. So while adding to an order I am separating as a line item and primary key is identity column so it generates a unique no. for each line item so system is working fine. The only thing I wanted to know that instead of using cursor as ah.nasr has replied, is there any other way of doing this .

  • January 26, 2009 at 5:07 am

    #932526

    aziz.kapadia (1/26/2009)

    There are two type of items one is Serialized like IMEI, SIM etc. and non serialized items like battery, charger etc.. Serialized item always going to be one in one kit but non serialized may be more than one. So while adding to an order I am separating as a line item and primary key is identity column so it generates a unique no. for each line item so system is working fine. The only thing I wanted to know that instead of using cursor as ah.nasr has replied, is there any other way of doing this .

    Do the non-serialised items have a way of idetinying them ? something like a product id

  • January 26, 2009 at 5:08 am

    #932528

    What I've replied with is a traditional loop not cursor at all.

    Check this article

    [/url]

  • January 26, 2009 at 5:55 am

    #932541

    by using a Tally or Numbers table, you could insertall the items, with the proper qty's in in a single statement:

    CREATE TABLE #TEMP (QTY INT, ORDERITEM VARCHAR(30),MORESTUFF VARCHAR(30) )

    INSERT INTO #TEMP

    SELECT 4,'MYWIDGET','SER0001'

    INSERT INTO #TEMP

    SELECT 1,'otheritem',''

    select ORDERITEM,MORESTUFF

    from #TEMP

    INNER JOIN TALLY ON QTY BETWEEN 1 AND QTY

    WHERE TALLY.N BETWEEN 1 AND QTY

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • January 26, 2009 at 5:58 am

    #932542

    Thanks ah.nasr

    Its working,

    I am sorry I didn't under stand previously.

  • January 26, 2009 at 7:17 am

    #932581

    Thanks to all for replying this post. Lowell's post was really helpful. I was looking for this kind of solution. Thanks

  • January 26, 2009 at 7:38 pm

    #932960

    ah.nasr (1/26/2009)

    set @count = 0

    select quantity from .....

    while @count < quantity

    begin

    set @count = @count + 1

    insert .......

    end

    Heh... try THAT on a million invoices...

    Lowell has it right with the Tally table solution.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • January 28, 2009 at 2:19 pm

    #934194

    try this out

    insert into orderkititems(...)

    select a.* from

    (select 'item' as item,col1,col2) a,

    (select top (select quantity from kitDetails where item='item') '' as row from sys.all_objects) b

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply