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:
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:
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 (‘).
Press or click the checkmark, then do a Fill/Down command:
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!