row-by-row multiple insert

[jamesagnew]

I've been racking my brains for several days over this problem so I hope someone might be able to give me a nudge in the right direction.

I have a simple sql 2000 table as below

ID - StartDate - EndDate (table_1)

1 - 01/02/03 - 05/02/03

2 - 17/04/04 - 24/05/04

...

I need to look at ID=1, insert all the dates from 01/02/03 --> 05/02/03 into another table, and then repeat the process for the other records (id=2, id=3 etc), giving the following results:

ID - DateIntervals (table_2)

1 - 01/02/03

1 - 02/02/03

1 - 03/02/03

1 - 04/02/03

1 - 05/02/03

2 - 17/04/04

2 - 18/04/04

2 - 19/04/04

2 - 20/04/04 ...etc


I've got a basic insert working using DATEADD for each consecutive date, but the problem is being able to run the insert after each indivdual record in the first table - I'm stuck on how to look at an individual record i.e. id=1, perform an insert based on the value of id, and then move onto the next record. Can I do this without cursors? I especially don't want cold fusion handling this, so it needs to be performed by the db.

Any help greatly appreciated !

David

 

[bperry]

Hi there.
The solutions I've seen for this situation all involve using a table of consecutive numbers (1,2,3...). My example below has a table called Numbers, with a column called num.

There need to be enough numbers (i.e. rows) in this table to cover the difference between any two dates you might have. So, if you could have a StartDate and EndDate that are 900 days apart, then you need at least 900 numbers in your table.

In my example, Numbers is a permanent table. You could also build this table as temporary. But this kind of table has uses in lots of situations, so it's often handy just to keep it hanging around.

Your Numbers table might start with 0 or 1.
The code changes a bit, depending on which.
----------------------------------------------
-- This version creates a new table called
-- NewTable and inserts the records into it.
-- First number in Numbers table is 0
----------------------------------------------
Select PkId,
dateadd(day,n.num,i.startdate) as DateInterval
into NewTable
from oldTable i Inner join numbers n
on i.startdate + n.num <= i.enddate
----------------------------------------------
-- Same thing, but Numbers table starts with 1
----------------------------------------------
Select PkId,
dateadd(day,n.num - 1,i.startdate) as DateInterval
into NewTable
from oldTable i Inner join numbers n
on i.startdate + n.num <= i.enddate + 1
--------------------------------------------------
-- In this version, NewTable already exists
-------------------------------------------------
Insert into NewTable
Select PkId,
dateadd(day,n.num - 1,i.startdate) as DateInterval
from oldTable i Inner join numbers n
on i.startdate + n.num <= i.enddate + 1
-------------------------------------------------

hope this helps,
brian perry

 

[bperry]

I was thinking that another possiblity for the Numbers table would be to generate it on the fly as a derived table.
Can't say I really care for that myself, but here's an example of how it would be done.

Select PkId,
dateadd(day,n.num,i.startdate) as DateInterval
into NewTable
from oldTable i

INNER JOIN

(
Select TOP 100 (a0.id + a1.id) as num
FROM
(
Select 0 as id UNION ALL
Select 1 UNION ALL
Select 2 UNION ALL
Select 3 UNION ALL
Select 4 UNION ALL
Select 5 UNION ALL
Select 6 UNION ALL
Select 7 UNION ALL
Select 8 UNION ALL
Select 9
) as a0

CROSS JOIN

(
Select 0 as id UNION ALL
Select 10 UNION ALL
Select 20 UNION ALL
Select 30 UNION ALL
Select 40 UNION ALL
Select 50 UNION ALL
Select 60 UNION ALL
Select 70 UNION ALL
Select 80 UNION ALL
Select 90
) as a1

order by num
) n
on i.startdate + n.num <= i.enddate
order by PkId, DateInterval
---------------------------
rgrds, etc
brian perry