Prepare SQL update Query in Excel
Sometimes when you want to update data into SQL database which is already in excel, updating records one by one can be cumbersome.
This also applies when you want to insert statements in SQL database which is in excel
Luckily you can generate SQL insert / update statements which you can later execute on the database console.
Below is the tutorial that will guide you to generate SQL update statements in excel.
The example below shows a simple update statement prepared from sales data in a table. But you can easily extend this method to prepare complex query statements depending on your data.
My data in excel is arranged like this
Let say you want to update the Unit Cost column in the SQL database, where the name of the item is as in the column Item
The query would look like below
Once the data is ready it is very easy to generate the SQL queries using excel string addition operator – &. For the above tabular structure, the concatenate formula would look like:
The final prepared queries will look like:
Formula: = “update Sales set UnitCost = ‘” & E2 & “‘ where Item = ‘” & C2 & “‘”;