Insert Statement

I have a table contain the following fields

Emp_Id (Employee Id)
Fin_Month (Finance Month)
Fin_Year (Finance Year)
Hol_Opening_Bal (Holiday Opening Balance)

I want to write a Insert Statement to add new records
but I have a problem that some employees added by application automatically and some other not added for month Aug and Year 2011

How I can make Insert Statement to add some employees for Month Aug and Year 2011

Thats in Oracle
egovernmentAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

OP_ZaharinCommented:
- is this that you mean:

INSERT INTO tablename
(Emp_Id, Fin_Month, Fin_Year, Hol_Opening_Bal)
VALUES
(val1, 'AUG', '2011', val4)

- need more detail such as does Fin_Month and Fin_Month is a date datatype? if yes try the following:

INSERT INTO tablename
(Emp_Id, Fin_Month, Fin_Year, Hol_Opening_Bal)
VALUES
(val1, to_date('AUG',MON'), to_date('2011',YYYY'), val4)
0
OP_ZaharinCommented:
- correction on the 2nd INSERT statement:

INSERT INTO tablename
(Emp_Id, Fin_Month, Fin_Year, Hol_Opening_Bal)
VALUES
(val1, to_date('AUG','MON'), to_date('2011','YYYY'), val4)
0
egovernmentAuthor Commented:
Hi OP_Zaharin

I want in one statement to add over 29 employee

Your statement I will add employee one by one
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

egovernmentAuthor Commented:
See

I have 200 employee

most of them added Aug/2011 to them records but still available 40 not added

How I can make insert statement to add for the 40 employee
0
OP_ZaharinCommented:
- you need to insert it one by one, you could create an insert script then paste it in sqlplus/sql editor of your choice all at once such as:
INSERT INTO tablename (Emp_Id, Fin_Month, Fin_Year, Hol_Opening_Bal) VALUES (val1, to_date('AUG',MON'), to_date('2011',YYYY'), val4);
INSERT INTO tablename (Emp_Id, Fin_Month, Fin_Year, Hol_Opening_Bal) VALUES (val11, to_date('AUG',MON'), to_date('2011',YYYY'), val44);
INSERT INTO tablename (Emp_Id, Fin_Month, Fin_Year, Hol_Opening_Bal) VALUES (val111, to_date('AUG',MON'), to_date('2011',YYYY'), val444);

- OR if you have the 40 data in a textfile with comma delimetered format (csv) you can use sqlloader to load the data all at once:
http://www.orafaq.com/wiki/SQL*Loader_FAQ
http://psoug.org/reference/sqlloader.html
0
maeltarCommented:
INSERT INTO tablename (Emp_Id, Fin_Month, Fin_Year, Hol_Opening_Bal) VALUES (val1, to_date('AUG',MON'), to_date('2011',YYYY'), val4)(val111, to_date('AUG',MON'), to_date('2011',YYYY'), val444)etc....

Open in new window

0
Shaju KumbalathDeputy General Manager - ITCommented:
1) Are u carryforwarding all employees from July to Aug 2011?

2) How is ur opening balance is calculated.
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Can you write a SELECT to get those 40 employees? In that case it would be something like
insert into tbl (Emp_Id, Fin_Month, Fin_Year, Hol_Opening_Bal)
select Emp_ID, to_date('AUG',MON'), to_date('2011',YYYY'), val4 from sometable

Open in new window

You could e.g. use the prior month to get those records, combined with a "NOT IN" subquery on TBL for 'AUG' records.
0
Shaju KumbalathDeputy General Manager - ITCommented:
1) Are u carryforwarding all employees from July to Aug 2011?

2) How is ur opening balance is calculated.

if u r carryforwardig the all employees and some are already present in august (as mentioned), then we can construct a select query for inserting . Only thing is you need to develop logic for calculating opening balance.
0
egovernmentAuthor Commented:
Hi shajukg

I'm looking to build special query to handle insert all employee not added them holidays in Aug/2011.

About How is ur opening balance is will be calculated in the insert statement that will be added 5 days for each employee.
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Something like this (not considering the balance stuff, as I did not get what you mean):
insert into tbl (Emp_Id, Fin_Month, Fin_Year, Hol_Opening_Bal)
select Emp_ID, to_date('AUG',MON'), to_date('2011',YYYY'), 5 from tbl where fin_year = to_date('2011', 'YYYY') and fin_month = to_date('JAN','MON') and emp_id not in (select emp_id from tbl where fin_year = to_date('2011', 'YYYY') and fin_month = to_date('AUG', 'MON'))

Open in new window

0
qasim_mdCommented:
Check this out, let me know if it helped:

INSERT INTO tablename
select emp_id,'AUG',2011, Hol_Opening_Bal + 5--Opening Balance added with 5 days.
from tablename where Fin_Month = 'JUL' and Fin_year = 2011
and emp_id not in (select emp_id from tablename where Fin_Month = 'AUG' and Fin_year = 2011)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
awking00Commented:
I think some sample data showing some employees that were added and some employees that were not added and what you would like it to be after the insert would be most helpful in supplying you with a solution.
0
awking00Commented:
Including datatypes would also help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

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.