insert loop -- I"m missing the obvious

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.

Re: insert loop -- I"m missing the obvious

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
wrote:

>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.

Re: insert loop -- I"m missing the obvious

eighthman11 (rdshultz@nooter.com) writes:
> 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.

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@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: insert loop -- I"m missing the obvious

On Tue, 10 Jul 2007 17:20:57 -0400, Roy Harvey
wrote:

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

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