row-by-row multiple insert (to replace earlier post!)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

row-by-row multiple insert (to replace earlier post!)

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-04-05 : 08:04:33

David writes "Please accept apologies for repeat post - I realised that my over-simplification in the earlier post (using ints rather than dates) might actually make this more confusing!

So here in all it's troubled glory is the actual problem!

---

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 "

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-04-05 : 08:20:35

2 options/suggestions for you.

1...join table1 to table3 (which contains every possible date range that table1 can handle)...with the join condition restricting the startdate and enddate...may(will) not be efficient...will look like a cross-product (trimmed down)

2...look into using a while loop???
other examples of same shown on other posts....could get around the 'no-cursors' problem.

something like...(provided your recordid is ascending numerical with few gaps.....then this'll be reasonably efficient)...

while @recordid < 99999999999999999 (or whatever would be the hightest recordid in the file)
select @startdate =startdate, @endate =enddate from table1 where recordid = @recordid
if not eof then
begin
@workdate = @startdate + 1 day
while @workdate < @enddate
insert into table2 values (@recordid, @workdate)
@workdate = @workdate + 1 day
loop
end

loop

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-04-05 : 08:56:38

Andrew's got the right idea, and Alexander has written code for this:

http://www.sqlteam.com/item.asp?ItemID=3332

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-04-05 : 09:01:12

Rob....3-4000miles apart....yet you're only just lurking around the corner!!!

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-04-05 : 10:40:34


SELECT ID, DATEADD(day, n, StartDate)
FROM table_1
INNER JOIN Numbers
  ON n BETWEEN 0 AND DATEDIFF(day, StartDate, EndDate)

Where Numbers is a sufficiently large table of integers.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources