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:

  1. 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).
  2. 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:

Sample - Generate Insert SQL From Excel (Screenshot 1)
Sample – Generate Insert SQL From Excel (Screenshot 1)
Sample - Generate Insert SQL From Excel (Screenshot 2)
Sample – Generate Insert SQL From Excel (Screenshot 2)

 

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>