How to Use Excel to Script Database Table Updates

Share on FacebookTweet about this on TwitterShare on LinkedIn

Share on FacebookTweet about this on TwitterShare on LinkedIn

As a Database Developer, I’m frequently asked to modify or populate data in a database table, usually from a list in an Excel spreadsheet. Now there are several ways to import data into a database table – linked servers, ODBC, bcp, bulk insert, SSIS, etc., but server logistics can sometimes get in the way. I find the easiest way to apply data changes is to create TSQL statements right in the same spreadsheet wrapped around the data. They can then be copied into a script for later execution, or executed   immediately in SSMS (or your favorite database interface tool). Obviously this method can only be used within Excel limitations, but I’ve had no problem using it to modify or populate several thousands of rows.


For example, using the Person table from the infamous AdventureWorks sample database:

AventureWorks Sample Database

You receive a request (in the form of an Excel file), to change the value of EmailPromotion attribute to the corresponding value in the list:

Email Promotion Change Excel File

Starting in cell C2, enter the following text:

=”UPDATE Person.Person set EmailPromotion=”&b2&” WHERE BusinessEntityID=”&A2

Note: this example deals with only numeric data, character data must be wrapped in single quotes (‘).

EmailPromotion Example

Press or click the checkmark, then do a Fill/Down command:

EmailPromotion Excel Example 3

As you can see, column C now contains all of the update statements needed for changing the EmailPromotion values. Select, Copy, and Paste them where needed. Saving these commands into a script (.sql) is particularly useful when the same changes need to be applied across multiple   platforms/sites i.e., development, QC, production, etc.

This is just a very basic example of this technique; other, more complex SQL/DML statements can be  created depending on the requirement and only limited by your imagination. I hope you find it useful!