How to create a SQL Server insert script that has various variables,and conditions?

Question

I have very limited knowledge of creating SQL scripts for SQL Server. I need to create a pretty simple script that inserts downtime records to be posted for future dates within a database. We have daily maintenance so I'll need to post a notice for every individual day from mon-sat. There is a fixed time period of when it occurs midnight to 2AM. I'd like to create all the entries necessary for a year.

I also need to create a second script that posts notices about downtime for 1 day weekly (Sunday) which has a different backup time frame. The time frame of this weekly backup is midnight to 7AM. I'd like to create all the entries necessary for a year for this also.

The basic format of the database table I need to insert is noticetime, begintime, endtime, msgtext. Table name is downtime. All date fields except for the msgtxt which is a a text field.

How do I create a script that would increment the dates to valid days that meet the criteria as well and terminates when a specific day has been reached? Same for the weekly insert?

Answer 1

if I understood you correctly you would need something like this, see pseudo code below haven't tested it though:

declare 
    @l_maxdate datetime
   ,@l_date datetime

select @l_date = getdate(), @l_maxdate = '2014-12-31'

while @date < @l_maxdate
begin
    if datepart(dw,@l_date) = 1 -- sunday
        insert .... values .... using date variable

    if datepart(dw,@l_date) > 1 -- moday - saturday
        insert .... values .... using date variable

    select @l_date = dateadd(dd, 1, @l_date)
end