How to Insert multiple rows if quantity is more than 1

Question

How to Insert multiple rows if quantity is more than 1

aziz.kapadia

Old Hand

Points: 395
More actions
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?

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

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

Steve Jones - SSC Editor SSC Guru Points: 736479 More actions · Answer 1

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?

aziz.kapadia Old Hand Points: 395 More actions · Answer 2

aziz.kapadia

Old Hand

Points: 395

January 25, 2009 at 10:28 am

#932396

Yes exactly same row for three times

Andrew Gothard-467944 Hall of Fame Points: 3928 More actions · Answer 3

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

ah.nasr SSC Veteran Points: 215 More actions · Answer 4

set @count = 0

select quantity from .....

while @count < quantity

begin

set @count = @count + 1

insert .......

end

aziz.kapadia Old Hand Points: 395 More actions · Answer 5

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.

Robert-378556 SSCertifiable Points: 5917 More actions · Answer 6

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.

aziz.kapadia Old Hand Points: 395 More actions · Answer 7

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 .

steveb. SSC-Forever Points: 46733 More actions · Answer 8

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

ah.nasr SSC Veteran Points: 215 More actions · Answer 9

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

Check this article

[/url]

Lowell SSC Guru Points: 323518 More actions · Answer 10

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!

aziz.kapadia Old Hand Points: 395 More actions · Answer 11

Thanks ah.nasr

Its working,

I am sorry I didn't under stand previously.

aziz.kapadia Old Hand Points: 395 More actions · Answer 12

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

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 13

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)

richardq.s SSC Enthusiast Points: 198 More actions · Answer 14

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