Build Insert Statement using Excel

Question

I have a spread sheet with content as below.

EMPLOYEE_ID ATT_DATE    ATT_TIME
1           10/9/2012   10:47:00
1           10/9/2012   10:47:00
1           10/9/2012   10:48:00
1           10/9/2012   10:49:00
1           10/9/2012   10:55:00

I want to create insert statements from these values. so i wrote below formula to generate insert statements.

="INSERT INTO ATTENDANCE (EMPLOYEE_ID, ATT_DATE) VALUES('" & Attendance!A2 & "', to_date('" & TEXT(Attendance!B2,"dd-mmm-yyyy") & "','DD-MON-YYYY'));"

i want to add values in ATT_TIME column to above insert statements. but i'm unable to write formula correctly for that.

how could i add values in ATT_TIME column to my insert statements ?

PS: i'm using oracle database.

Ahmad · Accepted Answer · 2012-11-07 12:20:54Z

3

Did you try to_date('" & Attendance!C2 & "','HH24:MI:SS') ?

Here is the complete query

="INSERT INTO ATTENDANCE (EMPLOYEE_ID, ATT_DATE, ATT_TIME) VALUES('" & Attendance!A2 & "', to_date('" & TEXT(Attendance!B2,"dd-mmm-yyyy") & "','DD-MON-YYYY'),to_date('" & Attendance!C2 & "','HH24:MI:SS'));

edited Nov 7, 2012 at 12:20

answered Nov 7, 2012 at 6:44

Ahmad

12.8k7 gold badges53 silver badges98 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Bishan · Accepted Answer · 2012-11-07 07:32:48Z

1

i have found solution for my question.

="INSERT INTO ATTENDANCE (EMPLOYEE_ID, ATT_DATE, ATT_TIME) VALUES('"&Attendance!A2&"', to_date('"&TEXT(Attendance!B2,"dd-mmm-yyyy")&"','DD-MON-YYYY'), to_date('"&TEXT(Attendance!C2,"h:mm:ss")&"','HH24:MI:SS'));"

answered Nov 7, 2012 at 7:32

Bishan

15.9k54 gold badges175 silver badges270 bronze badges

Collectives™ on Stack Overflow

Build Insert Statement using Excel

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related