How to create X records where x comes from a column in another table.

Question

How to create X records where x comes from a column in another table.

Eric_Gagne

SSCrazy

Points: 2062
More actions
January 28, 2010 at 9:07 am

#229399

Hi All,
I need to create x records in a table where x comes from a column in another table.
Example:
[Code]
-- Table A
OrderNumber PackageID PackageQty
------------ ---------- ------------
1 A 1
1 B 1
2 A 1
2 B 2
[/code]
For each Order/PackageID I need to create a number of records equal to PackageQty in my 2nd table. In this case I would need 5 records.
The result I need is
OrderNumber PackageID
------------ ----------
1 A
1 B
2 A
2 B
2 B
Obviously there's more fields going in the 2nd table but that is irrelevant for this post. Can this be done without cursors and loops ?

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

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

You'd need a CROSS APPLY against a table holding some numbers (aka Tally table - please see the related link in my signature).

Based on a tally table the code would look like

DECLARE @t TABLE (OrderNumber INT, PackageID CHAR(1), PackageQty INT)

INSERT INTO @t

SELECT 1,'A', 1 UNION ALL

SELECT 1,'B', 1 UNION ALL

SELECT 2,'A', 1 UNION ALL

SELECT 2,'B', 2

SELECT *

FROM @t t

CROSS APPLY tally

WHERE t.PackageQty >= tally.n

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Eric_Gagne SSCrazy Points: 2062 More actions · Answer 2

Lutz thanks for the reply. I've learned about Tally tables here in the forum and I already have a few but the "Cross Apply" is new to me. I'm gonna look into that.

Thanks again.

LutzM SSC Guru Points: 107049 More actions · Answer 3

Gagne (1/28/2010)
Lutz thanks for the reply. I've learned about Tally tables here in the forum and I already have a few but the "Cross Apply" is new to me. I'm gonna look into that.
Thanks again.

For the first part: You're very welcome. 🙂

Regarding "I already have a few [tally table]": would you mind sharing why you have more than one?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Eric_Gagne SSCrazy Points: 2062 More actions · Answer 4

lmu92 (1/28/2010)
For the first part: You're very welcome. 🙂
Regarding "I already have a few [tally table]": would you mind sharing why you have more than one?

Just as a safety net when testing and debugging. That allows me to limit the number of records that will be read/inserted if I make a mistake somewhere. If I know it's impossible to have more than 5000 records then I use my Tally5000 table. If I had only one with 1 million records, a mistake in my code could lead to inserting 1 million rows.

Here's a simple example taken from a procedure that I use to build a 4-4-5 Fiscal Calendar. I know there can only be 366 days in a year so I use tally500.

Insert Into FiscalCalendar

(

CalDate, PeriodYear

)

Select top (@NumDays) DateAdd(Day, N, @PeriodStartDate),

year(DateAdd(Day, N, @PeriodStartDate))

from dba.dbo.Tally500 order by N