We help IT Professionals succeed at work.

SQL 2000 - for Loop to insert a range of dates and values at once

Terrace
Terrace asked
on
Medium Priority
583 Views
Last Modified: 2012-05-06
What is the best way to do this?

Have a table tblMax  with DateFund and Maxvalue fields.

Need to fill that table with range of dates from 02/12/2009 - 01/01/2010

DateFund        MaxValue
02/12/2009     0
02/13/2009     0
etc

I guess it would be some For Loop in SQL Analizer or Stored Procedure.

Can anyone write me a quick simple code for it?

Thanks
Comment
Watch Question

Commented:
declare @tmp datetime

set @tmp = '02/12/2009'

while @tmp <= '01/01/2010'
begin
   insert into yourTable(DateFund, MaxValue)
   values  (@tmp, 0)
   
   set @tmp = dateadd(day, @tmp, 1)
end

Commented:
a typo error occured...
chenge the dateadd part like the one below

set @tmp = dateadd(day, 1, @tmp)

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Commented:
CREATE TABLE tblMax (DateFund DATETIME NOT NULL PRIMARY KEY, MaxValue INT NOT NULL);

INSERT INTO tblMax (DateFund, MaxValue) VALUES ('20090212',0);

WHILE @@ROWCOUNT > 0
 INSERT INTO tblMax (DateFund, MaxValue)
 SELECT dt,0 FROM
  (SELECT DATEADD(DAY,DATEDIFF(DAY,t.DateFund,m.DateFund)+1,m.DateFund) dt
  FROM tblMax t,(SELECT MAX(DateFund) FROM tblMax) m(DateFund)) d
 WHERE dt BETWEEN '20090212' AND '20100101';
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.