Batch SQL From Excel (free utility)

An Excel utility which creates batch INSERT, UPDATE and DELETE SQL statements.

Make all of your data edits in the comfort of Excel, and create SQL for only the columns you specify. Then copy the results to a SQL Server query window to execute.

Given Excel data..



..creates batch SQL

How to use:

Excel headers must match the field names in your SQL table.

To get NULLs, clear the contents of the cells. If you are working with data copied from the output window of SQL Server, the literal text "NULL" may display. Clear those cells. What you see in Excel is what you get in the batch results.

If you have a lot of data but only want the first several rows to be generated into SQL statements, insert a blank row to stop at that point.

To create batch INSERT statements:

Select 1:N headers from your header row in Excel. They may be non-contiguous. (Hold down the Control key to select non-contiguous cells).

Press CTRL + SHIFT + I. Type the table name to insert into. Press OK. A new worksheet is created with your SQL.

While selecting the headers, one way to visualize the process is "INSERT INTO table [select headers] ...".

To create batch UPDATE statements: Selecting headers for this one is tricky at first, but once you get the hang of it you will see that it is quite flexible.

First, select 1:N headers from your header row in Excel as the update/SET fields. They may be non-contiguous. (Hold down the Control key to select non-contiguous cells).

Then hold down the Control key and select 1:N headers from your header row in Excel as the WHERE fields. They must be contiguous.

Press CTRL + SHIFT + U. Type the table name to update to. Press OK. A new worksheet is created with your SQL.

While selecting the headers, one way to visualize the process is "UPDATE these fields [select headers] WHERE [select headers] = ...".

To create batch DELETE statements:

Select 1:N headers from your header row in Excel. They may be non-contiguous. (Hold down the Control key to select non-contiguous cells).

Press CTRL + SHIFT + D. Type the table name to delete from. Press OK. A new worksheet is created with your SQL.

While selecting the headers, one way to visualize the process is "DELETE FROM table WHERE [select headers] = ...".

Notes:

The output is usable in SQL Server. Does not work in Access.

Access the commands by the shortcut keys as noted above, or the Tools menu --> Batch SQL From Excel.

You may want to use a transaction when you execute batch SQL.

The download file is an Excel Add-In. For instructions on how to install an Excel add-in see the "Load or unload add-in programs" topic in Excel help.

During download, Internet Explorer may try to change this file type to XLS even though it is an XLA. I don't know why it does this, but Firefox and Google Chrome seem to leave the file extension alone. If the file extension is changed, you may manually change it back to XLA after download.

Be sure to validate the output results before you use the SQL generated.
This utility is provided "AS IS" without any warranty of any kind. Use it at your own risk.

Download BatchSQLFromExcel1.6.xla (41K)
Last updated 2/10/09