Power of Excel spreadsheet to dynamically 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

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.

The generated SQL statement will be

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

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

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

300+ Java & Big Data Interview FAQs

16+ Java Key Areas Interview Q&As

800+ Java Interview Q&As

300+ Java & Big Data Tutorials