Pro Tip: Insert records into a SQL database from an Excel spreadsheet

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).

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.