Fellow Experts,

I am usually pretty handy with basic SQL operations, but I have a need for an operation
that requires some sort of loop and/or cursor/table variable/temp table approach that
needs to be as efficient as possible.

Here is what I need to do, in SQL Server 2000:

1. I have two tables, one each in two databases on the same server.

DatabaseA.dbo.ShiftHrs_Update
--------------------------------------------------------------------
shift_id uniqueidentifier
BlockStart datetime
BlockDur int
pattern_id uniqueidentifier

This table has 1 to 7 records for each shift; each record represents the working hours at a
particular company location.  Each location may be open different days of the week (and
thus a limit of 7 records for any given shift_id), and be open during various hours on the
days they are open.  BlockStart specifies the start date/time of the shift, and BlockDur is
the length of the shift in seconds.  The dates are all within a "reference week" that started
Sunday 4 Jan 2004.


DatabaseB.dbo.pattern_values
--------------------------------------------------------------------
pattern_id uniqueidentifier
sample_date datetime
sample_value float

This table is used to spread a forecasted weekly work requirement throughout a day, split into
half-hour intervals.  Thus, if I want to spread the workload evenly throughtout open hours, I
would have a record for each half-hour interval inside the shift's open hours, with the same
sample_value for each record.  The dates are all within a "reference week" that started
Sunday 4 Jan 2004.

2. For each record in DatabaseA.dbo.ShiftHrs_Update, I have to insert records in DatabaseB.dbo.pattern_values
such that for the time period specified in DatabaseA.dbo.ShiftHrs_Update I have every half-hour
interval covered in DatabaseB.dbo.pattern_values.  For example, if the record in DatabaseA.dbo.ShiftHrs_Update
is:

<shift_id>, 2004-01-05 09:00:00, 28800, <pattern_id>      (that is, Monday from 9am - 5pm)

Then I would need to insert the following in DatabaseB.dbo.pattern_values:

<pattern_id>,2004-01-05 09:00:00,1
<pattern_id>,2004-01-05 09:30:00,1
<pattern_id>,2004-01-05 10:00:00,1
<pattern_id>,2004-01-05 10:30:00,1
<pattern_id>,2004-01-05 11:00:00,1
<pattern_id>,2004-01-05 11:30:00,1
<pattern_id>,2004-01-05 12:00:00,1
<pattern_id>,2004-01-05 12:30:00,1
<pattern_id>,2004-01-05 13:00:00,1
<pattern_id>,2004-01-05 13:30:00,1
<pattern_id>,2004-01-05 14:00:00,1
<pattern_id>,2004-01-05 14:30:00,1
<pattern_id>,2004-01-05 15:00:00,1
<pattern_id>,2004-01-05 15:30:00,1
<pattern_id>,2004-01-05 16:00:00,1
<pattern_id>,2004-01-05 16:30:00,1

(Note that there is no record for the 5:00 pm interval, because that is the end time of the shift
that day.)

Any help will be greatly appreciated.  If anything above is unclear, please do not hesitate to ask
for clarification.  I will be away for a few hours, but will be checking back in in the early afternoon
during USA Eastern time.

Best regards,

Patrick