Matt Hughes tinkerer, grower, craftsman

Updating SQL from an Excel file

Coworker: "Can you update some values in the database for me?"

Me: "Sure, what do you need?"

Coworker: "Oh, I have it all here in this Excel spreadsheet."

Me: [hatred and disgust]

With some clever Excel formula work, you can generate an update (or insert) statement for each row in the spreadsheet, so you don't have to copy/paste 200 of them.

Assume you have a spreadsheet like this:


Input the following formula in column E, starting with the first row of data:

= "update TABLENAME set "
      &$B$1&"="&B2& ", "
      &$C$1&"="&C2& ", "
      &$D$1&"="&D2&
   " where "&$A$1&"="&A2

Drag the fill handle down to generate SQL statements for the rest of the rows.

Viola, now you can move on to something worth your time!