?
Solved

Insert Statement

Posted on 2011-10-24
14
Medium Priority
?
418 Views
Last Modified: 2012-06-21
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
0
Comment
Question by:egovernment
  • 3
  • 3
  • 2
  • +4
14 Comments
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 37022592
- 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
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 37022593
- 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
 

Author Comment

by:egovernment
ID: 37022634
Hi OP_Zaharin

I want in one statement to add over 29 employee

Your statement I will add employee one by one
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

Author Comment

by:egovernment
ID: 37022640
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
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 37022648
- 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
 
LVL 11

Expert Comment

by:maeltar
ID: 37022666
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
 
LVL 15

Expert Comment

by:Shaju Kumbalath
ID: 37023217
1) Are u carryforwarding all employees from July to Aug 2011?

2) How is ur opening balance is calculated.
0
 
LVL 71

Expert Comment

by:Qlemo
ID: 37023232
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
 
LVL 15

Expert Comment

by:Shaju Kumbalath
ID: 37023440
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
 

Author Comment

by:egovernment
ID: 37023458
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
 
LVL 71

Expert Comment

by:Qlemo
ID: 37023479
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
 
LVL 4

Accepted Solution

by:
qasim_md earned 2000 total points
ID: 37023561
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
 
LVL 32

Expert Comment

by:awking00
ID: 37024899
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
 
LVL 32

Expert Comment

by:awking00
ID: 37024909
Including datatypes would also help.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question