Monday, March 4, 2013

Generate SQL insert/update statements from Excel

Excel is a very helpful tool when you are trying to copy data between to non compatible environments.Once such situation is having data in a excel file and want to import it to a data base table.its very easy to generate insert/update commands in excel which can be directly executed in the console.

We have two options for import

1) Direct import option in SQL Developer

select the table in the left panel of the SQL developer,right click ,import.

This helps us a lot when data is in exact format of the table.how ever when we have data in pivots and complex formats this will not help.For such cases we have option 2.

2) Generate INSERT/UPDATE statements 

I will give you a simple example, the logic can be applied to any complex statements.

Lets consider below data ,which has to be inserted into a STUDENT TABLE (ROLL NO.NAME,BRANCH)


Now select a cell next to last column of the first row and write the below statement in it

="INSERT INTO STUDENT ("&B2 &",'"&C2 &"','"&D2 &"')"

Apply the same formula for all the rows by just dragging the cell.

You can see the result as below



This is simple and the logic applies for all complex queries.

1 comment: