Inserting Multiple Records

Question

Post reply

Inserting Multiple Records

tuffgongbob

SSC Enthusiast

Points: 141
More actions
December 8, 2008 at 6:34 pm

#197061

Hi everybody.
I'm looking to split up a record into multiple records on insert. For instance, say I have a merchandise order for 5 of a particular hat, and 4 of a particular shirt. E.g. I have the values "top hat", "$5.00", "5", and "tank top", "$8.00", "4", which correspond to type, cost, and quantity. I want to take these values and insert 5 records for the hat, and 4 records for the shirts. What's the most efficient way to accomplish this without using the split() function?
Best,
Mike

Viewing 4 posts - 1 through 4 (of 4 total)

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

Why do you need to do this? Wouldn't you have an order header with these 2 lines items on the order?

Are the $ amounts totals or price per item?

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

tuffgongbob SSC Enthusiast Points: 141 More actions · Answer 2

I essentially just want a way to take a row in a table that has a column of "quantity" and make individual rows in another table based off of that value. So if I had a quantity of "4", I want to take that row and make 4 entries in a different table.

So...

"top hat" "$5.00" "5" would be

"top hat" "$5.00"

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

I understood what you wanted to do, I just was wondering why you wanted to that. One way to do what you want would involve the use of a Numbers/Tally table[/url]. You could do something like this:

Declare @orders table (product varchar(15), quantity int, price decimal(9, 2))

Insert Into @orders

Select

'TOP HAT',

5,

5.00

Union All

Select

'Text',

7,

1.25

-- populates a temporary numbers table. You should

-- have a permanent one already if you don't you should

-- create one.

Select top 10

Identity(int, 1, 1) as n

Into

#nums

From

sys.columns C1 Cross Join

sys.columns C2

Select

O.product,

O.price,

O.quantity/O.quantity as quantity

From

@orders O Join

#nums N On

O.quantity >= N.n

Drop table #nums

Edit: You would put an insert into table before the last select to insert into another table.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Inserting Multiple Records

Cookies on SQLServerCentral