Monday, December 30, 2013

Adding Date column in SQL insert queries using Microsoft Excel 2007

Leave a Comment
In the previous article we learnt Creating SQL Insert Queries using Excel. We illustrated it by an example taking columns with varchar2 and numeric data type. Columns with date data type may also be required sometimes. In this tutorial we will learn how date column can be added in the SQL insert queries using excel.

Steps to add Date column in SQL Insert queries using Microsoft Excel 2007

1.       Added emp_dob column of Date data type to the emp_details table in the database. Same column has been updated in Microsoft excel.  Added values under the column.


2.       Now we have to create formula for column H, so that SQL insert query created fetches date with correct syntax. There are two ways for it.
a.       Formula 1: =CONCATENATE(G6, A6, ",",  "'", B6, "'", ",", "'", C6, "'", ",", "'", D6, "'", ",",E6,",", "to_date('", F6, "', 'mm/dd/yy')", ");")
This formula will create correct SQL insert query only when the values in excel are in 01/01/1900 format.

In our case we have emp_dob in 01-01-1980 format. In this case it will create SQL insert query with invalid date value.

Following insert query will be created using Formula 1 (Invalid date highlighted)
insert into emp_details (n_emp_id, v_emp_name, v_emp_role, v_emp_loc, n_emp_salary, emp_dob ) values(201,'Emp201','Role201','Loc201',100000,to_date('29221', 'mm/dd/yy')

So, we should avoid using Forumla1, as date may be entered in excel in any format by the user.

b.      Formula 2: =CONCATENATE(G6, A6, ",",  "'", B6, "'", ",", "'", C6, "'", ",", "'", D6, "'", ",",E6,",", "to_date('", TEXT(F6, "mm/dd/yy"),"', 'mm/dd/yy')", ");")

To overcome the problem in Formula 1 we use the Formula 2. It converts any date format to 01/01/1900 format and thus creates  SQL insert query with valid date.



Following insert query will be created using Formula 2 ():
insert into emp_details (n_emp_id, v_emp_name, v_emp_role, v_emp_loc, n_emp_salary, emp_dob ) values(201,'Emp201','Role201','Loc201',100000,to_date('01/01/80', 'mm/dd/yy'));

3.       Drag the formula across all rows and fire the insert query in the database. Emp_Dob column values successfully inserted in the database.





0 comments:

Post a Comment