Excel spreadsheet to generate SQL

When you have some data in tabular (e.g. Excel spreadsheet) format and would like to insert into a database table, you need to write an SQL insert query. Manually writing SQL query for multiple records can be cumbersome. This is where Excel spreadsheet comes in handy as demonstrated below. A single SQL query can be copied down where the formulas get copied with incrementing column numbers.

The  Excel concatenate character & is used to achieve this. The $ means fix. $a1 means fix excel column A. When you copy the formula, the row numbers will be incremented like 2,3,4, etc, but the column will remain fixed to A. In the example below

• $A$1 = first_name
• $B$1 = surname
• $C$1 = age

Note: Both column and row are prefixed with $, which means both are fixed.

The Excel formula is

 

="insert into person ("&$A$1&", "&$B$1&", "&$C$1&") values ('"&$A2&"','"&$B2&"',"&$C2&")"

The above Excel expression is easier to understand if broken down as shown below where the concatenation character & plays a major role in combining static text within quotes with dynamic formulas like $A$1.

 
"insert into person ("

&

$A$1

&

", "

&

$B$1

&

", "

$C$1

&

") values ('"

&

$A2

&

"','"

&

$B2

&

"',"

&

$C2

&

")"

The generated SQL statement will be

 

insert into person (first_name, surname, age) values ('Peter','Smith',35)

This SQL can be copied down in Excel to get SQL for all rows

 

insert into person (first_name, surname, age) values ('Peter','Smith',35)    
insert into person (first_name, surname, age) values ('John','Smith ',12)    
insert into person (first_name, surname, age) values ('Eddy','Wayne',32)    

You can create other SQL statements using the above technique from a table of data for better productivity.


If you have date column, the use the following formula to convert it to a text.

TEXT($D2,"dd/mm/yyyy")

You may also like:

• Database interview Questions and answers
• SQL Interview Questions and Answers
•  SQL interview questions and answers - scenarios based
•  SQL Interview Questions and Answers on deleting records
•  SQL subqueries interview questions and answers
•  SQL Interview Questions and Answers: storing a tree structure in a database table
•  SQL Tutorial with HSQLDB