add data from two tables into one table

I have a table called employees: within it I have various fields two of which being:

emp_uid, and emp_shift_duration.

emp_uid is unique.

emp_shift_duration varies a bit becasue most emps work either 4 hours, 5 hours, 6 hours or 8 hours.

Sometimes people are out and I want to use their hours to do special projects so I want to keep tabs on when they miss hours. The shift sup. can keep track of this but in order to save time I only want him to 'edit' the missing hours when needed, not add hours eveyday so I want the table populated ahead of time.

so I created another table which is called :

emp_hours_worked: fields are 3 : emp_uid, projected_hours,and date.

I wish to populate emp_hours_worked with employee data from employees table.
emp_uid, and emp_shift_duration; and also populate date with the days of the year for 'each' employee: this will be a very long table because the result will be that each employee will have a record for each date of the year.: therefore using a simple php script the sup. can go in and edit any necessary dates and then I will run a query to sum up the hours for the current pay weeks.

emp_hours_worked will look something like this.

emp_uid,projected_hours,date

3432,4,01-01-2013
3432,4,01-02-2013
3432,4,01-03-2013...etc.

7715,8,01-01-2013
7715,8,01-02-2013...etc...  or ....

3432,4,01-01-2013
7715,8,01-01-2013
9824,5,01-01-2013
9954,8.01-01-2013... etc. depending how its shown but did this to get the idea through.

So I am looking for sql statement to 'insert' all this data into the emp_hours_worked table.

Well anyways this is my idea any better solutions would be welcome.
opelrajaAsked:
Who is Participating?
 
oleggoldCommented:
insert into emp_hours_worked
(select emp_uid, to_date(emp_shift_duration,'hh:mi:ss'),trunc(emp_shift_duration) from employees e)
0
 
oleggoldCommented:
I don't see any other table for the select's join condition,in case it's there use
'employees e left join <other tab t> on field'
0
 
opelrajaAuthor Commented:
I figured it out like this:

I created a table 'dates with just '_dates' as a field : being the whole year day to day of 2013.

then I used this query:

Insert into emp_hours_worked(emp_uid,projected_hours,_date)
SELECT emp_uid, emp_shift_duration,_date
FROM employees_table AS a, date AS b;

So it populated a line for each employee for each day. Now I just need a faster laptop for this huge table.
0
 
keyuCommented:
if everytime you are creating new table then go for this...

SELECT emp_uid, emp_shift_duration as projected_hours,_date into emp_hours_worked FROM employees_table AS a, date AS b

if you already have table and wants to make entries than go for this as mentioned by you in your previous comment....

Insert into emp_hours_worked(emp_uid,projected_hours,_date)
SELECT emp_uid, emp_shift_duration,_date
FROM employees_table AS a, date AS b;
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.