I have a table on sql server. It has three columns (1-Make) (2-Model)
(3-Quantity Ordered). I'm simplifying this a bit.

Sample data:

Ford Mustang 3
Chevy Camaro 2

I just need a stored procedure that I can run that will insert those
records into a workfile based on the Quantity ordered, so if you have
3 ordered in the first table like above it will insert the columns
Ford and Mustang into the workfile 3 times (3 records) and likewise
the Chevy record Twice for (2 records)

Example results of workfile:

Ford Mustang
Ford Mustang
Ford Mustang
Chevy Camaro
Chevy Camaro

This can't be that difficult but I am not a sql programmer. Any help
would be appreciated.

You need a Numbers table with the integers 1 through at least the
maximum number from the QuantityOrdered column. Then you can simply:

SELECT Make, Model
FROM TheTable as A
JOIN Numbers as B
ON N.i BETWEEN 1 AND A.QuantityOrdered

Roy Harvey
Beacon Falls, CT

On Tue, 10 Jul 2007 14:01:54 -0700, eighthman11 <rdshultz (AT) nooter (DOT) com>
wrote:

eighthman11 (rdshultz (AT) nooter (DOT) com) writes:
Actually it is not that straightforward, and there is a reason for this:
you would normally not do this. A table is supposed to have a primary key
which uniquely identifies the data, so insert several rows with the same
value isn't exactly normal.

You call this a "workfile" - I assume that this is a temp table of
some sort. Exactly what you will achieve in the end, I don't know. But
it might be that you have approached the actual business problem in the
wrong way.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

On Tue, 10 Jul 2007 17:20:57 -0400, Roy Harvey <roy_harvey (AT) snet (DOT) net>
wrote:

That last line should have tested B.i, not N.i. The column i is the
integer number column of the Numbers table.

Roy Harvey
Beacon Falls, CT