Insert Multiple records using loop

Question

I need to add a set of records (dates) to a calander table....I know there is a way to loop through and add a year dynamically but not sure of the syntax... can someone help?

declare @dt  datetime
set @dt = '2011-01-01'

insert into  dbo.T_DM_PRC_EFF_DT  values(@dt+1,@dt+1)
insert into  dbo.T_DM_PRC_EFF_DT  values(@dt+2,@dt+2)

Answer 1

0

You want to avoid using loops - that's not what SQL was created for. If you can use a set-based approach, you should use it.

Your specific question is yet another use for a Tally table:

EDIT I've updated my answer, based on the comment made, that the first column should contain consecutive dates, and the second should contain the same value when weekday is Mon-Fri, otherwise the next workday. END EDIT

--Change to TOP whatever_number_of_rows_you_need
SELECT top 2000 IDENTITY(INT,1,1) as N
INTO Tally
FROM master.sys.columns c1, master.sys.columns c2
ALTER TABLE Tally ADD CONSTRAINT PK_Tally PRIMARY KEY (N)
GO
SET DATEFIRST 1
DECLARE @dt datetime
SET @dt='2011-01-01'
INSERT INTO dbo.T_DM_PRC_EFF_DT
SELECT TOP 100 
    @dt + N -1,
    CASE DATEPART(weekday,@dt + N-1) WHEN 6 THEN @dt+N+1 WHEN 7 THEN @dt + N ELSE @dt +N -1 END
FROM Tally 
ORDER BY N

answered Dec 30 '10 at 01:28 PM

Magnus Ahlkvist
13.7k ● 13 ● 17 ● 30

Magnus thank you so much...I am troubleshooting and over the hump of the immediate crisis...

I have a two column table that has prc effective date and col 2 = next prc eff date.

the next prc eff date considers the weekend so how can I build the day of week logic into the table load that says if the dw = 6,7 then use the next monday day of week?

Dec 30 '10 at 01:46 PM siera_gld

Add

WHERE DATEPART(weekday,@dt + N -1) not in (6,7)

to the query. You might want to add

SET datefirst 1

to the beginning of the batch as well, to make sure you get sunday treated as day seven and not day 1.

Dec 30 '10 at 01:58 PM Magnus Ahlkvist

Or if you mean that you want one column containing all dates (incl. saturdays and sundays) and the other column containing the first workday after the first column, you'd want:

set datefirst 1
declare @dt datetime
set @dt='2011-01-01'
select top 100 
    @dt + N -1,
    CASe datepart(weekday,@dt + N) when 6 then @dt+N+2 when 7 then @dt + N +1 else @dt +N end
FROM Tally 
order by N

Dec 30 '10 at 02:09 PM Magnus Ahlkvist

Almost - it displays this

PRC_EFF_DT           NEXT_PRC_EFF_DT
2011-07-21 00:00:00.000 2011-07-21 00:00:00.000
2011-07-24 00:00:00.000 2011-07-24 00:00:00.000

I need it to load this

PRC_EFF_DT            NEXT_PRC_EFF_DT
2011-07-21 00:00:00.000 2011-07-21 00:00:00.000
2011-07-22                2011-07-22
2011-07-23 00:00:00.000 2011-07-25 00:00:00.000
2011-07-24 00:00:00.000 2011-07-25 00:00:00.000
2011-07-25 00:00:00.000 2011-07-25 00:00:00.000

Do you see how my next eff dt column skips gthe weekends but my eff date does not? I also need to adjust the weekday to 7,1

Dec 30 '10 at 02:26 PM siera_gld

That's almost what my last comment does. I was one day wrong in the second column. This should be right. I'll update my answer with the code as well

set datefirst 1
declare @dt datetime
set @dt='2011-01-01'
select top 100 
    @dt + N -1,
    CASe datepart(weekday,@dt + N-1) when 6 then @dt+N+1 when 7 then @dt + N else @dt +N -1 end
FROM Tally 
order by N

Dec 30 '10 at 02:37 PM Magnus Ahlkvist

(comments are locked)

Insert Multiple records using loop

Follow this question

Related Questions