Here’s a process I’ve been using for years to load data into a SQL database from Excel (and possibly do some transformation in the process).
It may seem obvious to some people but I have known software developers who never considered it.
It’s simple:
- Write an INSERT SQL statement on each line of the sheet you need to import (do it for the first row, then use Excel’s “fill down” feature).
- Copy those SQL statements into your SQL database management tool of choice, and execute.
For example, if we have the following table row (green heading), I can add the column “Location Lookup SQL” column which contains a formula to create SQL string by concatenating values from other columns.
| LOCATION | CATEGORY | RATING | FROM | TO | Location Lookup SQL | |
| NWS | A | Moderate | 2000 | 7999 | =”SELECT ID FROM [LOCATION] WHERE [Name] = ‘”&A2&”‘” |
If you find copying the “Location Lookup SQL” pastes the formula and not the resultant SQL value into your database manager, then a simple workaround is to copy the whole “Location Lookup SQL” column (all rows), then paste into another column in the spreadsheet using the “Paste Special” option and past only the values.
A sample is always good to illustrate so here’s an adaptation of an Excel file I just worked on: Sample – Generate Insert SQL From Excel
Or, if you prefer a quick look at it in pictures:
