How to insert 4 records for every one found?

This SQL will insert one record into #Pivot1 for every one
record returned by the SELECT statement:
---
INSERT INTO #Pivot1
(
PersonID,
ProjectID,
Estimate5,
Available5
)
SELECT
wr.PersonID,
ln.ProjectID,
hr.Hours,
wr.HoursAvailable
FROM (tblWeekReported wr
INNER JOIN tblWeekReportedLine ln ON wr.WeekReportedID = ln.WeekReportedID)
INNER JOIN tblHour hr ON ln.WeekReportedLineID = hr.WeekReportedLineID
WHERE
(
(wr.BeginDate=@.BeginDate5) AND
(wr.EndDate=@.EndDate5) AND
(wr.WeekType=2) -- 2=Monthly
);
---
What I need to do, however, is to split each of the incoming records up
into 4 inserts to #Pivot1, dividing 'Hours' and 'HoursAvailable' by 4
for each record.
I guess the brute force method would be to just run that snippet of SQL
four times... but is there a "right" way?
--
PeteCresswellOops! I think I may have something:
---
INSERT INTO #Pivot1
(
PersonID,
ProjectID,
Estimate5,
Available5
)
SELECT
wr.PersonID,
ln.ProjectID,
hr.Hours/4,
wr.HoursAvailable/4
FROM (tblWeekReported wr
INNER JOIN tblWeekReportedLine ln ON wr.WeekReportedID = ln.WeekReportedID)
INNER JOIN tblHour hr ON ln.WeekReportedLineID =hr.WeekReportedLineID
WHERE
(
(wr.BeginDate=@.BeginDate5) AND
(wr.EndDate=@.EndDate5) AND
(wr.WeekType=2) -- 2=Monthly
);
INSERT INTO #Pivot1 SELECT * FROM #Pivot1;
INSERT INTO #Pivot1 SELECT * FROM #Pivot1;
INSERT INTO #Pivot1 SELECT * FROM #Pivot1;
---
Am I on the right track?
--
PeteCresswell|||RE/
>INSERT INTO #Pivot1 SELECT * FROM #Pivot1;
>INSERT INTO #Pivot1 SELECT * FROM #Pivot1;
>INSERT INTO #Pivot1 SELECT * FROM #Pivot1;
"Oops" again: strike that last INSERT - it would
have given us 8 copies of each rec instead of 4...
--
PeteCresswell|||That wouldn't work, you'd end up with far too many records.
--
HTH
Ryan Waight, MCDBA, MCSE
"(Pete Cresswell)" <x@.y.z> wrote in message
news:0k3vqv0fr0s2763bmgfma27re6e4lbglhc@.4ax.com...
> Oops! I think I may have something:
> ---
> INSERT INTO #Pivot1
> (
> PersonID,
> ProjectID,
> Estimate5,
> Available5
> )
> SELECT
> wr.PersonID,
> ln.ProjectID,
> hr.Hours/4,
> wr.HoursAvailable/4
> FROM (tblWeekReported wr
> INNER JOIN tblWeekReportedLine ln ON wr.WeekReportedID =ln.WeekReportedID)
> INNER JOIN tblHour hr ON ln.WeekReportedLineID => hr.WeekReportedLineID
> WHERE
> (
> (wr.BeginDate=@.BeginDate5) AND
> (wr.EndDate=@.EndDate5) AND
> (wr.WeekType=2) -- 2=Monthly
> );
> INSERT INTO #Pivot1 SELECT * FROM #Pivot1;
> INSERT INTO #Pivot1 SELECT * FROM #Pivot1;
> INSERT INTO #Pivot1 SELECT * FROM #Pivot1;
> ---
> Am I on the right track?
> --
> PeteCresswell|||On Mon, 10 Nov 2003 13:05:03 GMT, "(Pete Cresswell)" <x@.y.z> wrote:
>This SQL will insert one record into #Pivot1 for every one
>record returned by the SELECT statement:
>...<snip>...
>What I need to do, however, is to split each of the incoming records up
>into 4 inserts to #Pivot1, dividing 'Hours' and 'HoursAvailable' by 4
>for each record.
>I guess the brute force method would be to just run that snippet of SQL
>four times... but is there a "right" way?
I don't know about right way, but here's a couple of ways:
a) after inserting these rows, select them and insert again (thus
doubling them). Then select them and insert them once more (thus
doubling the double, i.e. four rows for each original one)
b) create a temp table, populate with four records, and join to it in
the select part of your statement. A cartesian join (i.e. no criteria
for the join so that all rows from your temp table are selected and
joined with all rows from the rest of your query) will mean that each
row will appear four times in your result set. This would be my
preference, as it involves a single insert (except for the temp table :)
Of course, if you did this regularly enough, you might want to leave the
table of four rows around permanently, ready to join with. Kind of like
the DUAL table in Oracle these days, that provides a hack method for
returning results from functions without referencing a "real" table.
cheers,
Ross.
--
Ross McKay, WebAware Pty Ltd
"Words can only hurt if you try to read them. Don't play their game" - Zoolander