Using Excel to dynamically generate flexible SQL script, fast

Sometimes there is a need to quickly “hack” some SQL for updating many records. Using Excel as a tool to do this allows the power of Excel formulas and filtering to achieve quick generation of SQL. Often it can be quicker to use this method to generate lots of simple SQL statements than it would be writing a sophisticated SQL query to do the same thing as one statement. The excel sheet can be saved for reuse in the future, or be linked to refreshable live data for regular updates.

It should be noted that running SQL against production environments is risky, so please ensure you test the generated script first.

Although this is a simplistic example to show the technique, imagine how this can be built upon, using Excel formulas, Excel filters, or referencing different sheets, it is possible to get complicated updates ready very quickly.

If required, “parameters” can be entered in the sheet where regular admin tasks are required (although there are probably better approaches it can bridge a gap until the better solution is in place.

How to do it

Query in SQL management studio the base data for the task. in this example we are aiming to update all the product descriptions, adding the item number to the end of those descriptions. In SQL management studio all the item numbers and descriptions are selected, copy all the cells then paste into a fresh Excel sheet.

Now on the first unused column, use  the CONCATENATE statement to join together strings for the required SQL. In this case it can be seen how the UPDATE statement is built, it references the data pasted into the sheet. This is a simple example, it gets much more fun when you start using Excel features like filters, and more complex formula.

Once the formula has been “copied down” the column, simply copy the contents of the column. Now in a fresh query window in SQL Management Studio simply paste the SQL back in and then execute the SQL script against the database. Obviously test, test, test before committing.

I use this method a lot for a variety of SQL updates where the problem cannot be quickly expressed as a TSQL query. Much as I love writing complex queries, sometimes it is not a good use of time, especially for one off executions.