Automatic Insertion of Data

Question

I have a table name Overtime Hours which have the following columns Ot_ID, Shift_Date, Employee_ID, Hours.

What I need to do is insert a set of values in these tables on the 1st date of every month, automatically.

for example, I need to add values('1/1/2010',12345,4.6) for january, values('2/1/2010',12345,4.6) for february and so on for the whole year.

This addition has to be done so a certain list of employees only and the Hours value is constant for every month. I am working on MS SQL Server 2000 in the backend. and visual studio, Winforms on C sharp in the front end.

Answer 1

You can create a list of months using a common table expression. Use cross join to generate a row for each employee for each month, and insert that into the hours table.

Some example code, with table variables:

declare @hourtable table (
    ot_id int identity(1,1),
    shift_date datetime,
    employee_id int,
    hours float)

declare @employees table (
    employee_id int
)   

insert into @employees select 1
insert into @employees select 2

;with months as (
    select cast('2009-01-01' as datetime) as month
    union all
    select dateadd(m,1,month)
    from months
    where month < '2009-12-01'  
)
insert into @hourtable
(shift_date, employee_id, hours)
select m.month, e.employee_id, 1.23
from months m
cross join @employees e

select * from @hourtable

Answer 2

The SQL Server Agent Service can schedule your job (of inserting new records) to be carried out every month; this can be done entirely within MSSQL2000 and doesn't need any front-end programming.