A 30-minute database update turned into half a morning trying to explain something that shouldn't have been possible.
It was supposed to take 30 minutes.
A user needed some data corrected before they could generate a report. They sent us an Excel file with the records that needed to be updated.
Nothing unusual.
I needed to turn those rows into SQL UPDATE statements.
So I did what many developers have probably done at some point: I built an Excel formula, dragged it down, copied the generated SQL into a file, checked a few statements, and sent it to Production.
The generated SQL looked completely normal:
UPDATE Customers SET Status='Pending', UpdateDate='2026-08-10' WHERE CustomerId=10482;
UPDATE Customers SET Status='Approved', UpdateDate='2026-08-10' WHERE CustomerId=10483;
UPDATE Customers SET Status='Review', UpdateDate='2026-08-10' WHERE CustomerId=10484;
UPDATE Customers SET Status='Pending', UpdateDate='2026-08-10' WHERE CustomerId=10485;
UPDATE Customers SET Status='Approved', UpdateDate='2026-08-10' WHERE CustomerId=10486;
-- ... many more
Valid SQL. Valid IDs. Valid statuses. Valid dates.
Nothing jumped out.
Production executed it. No errors. SQL Server was happy.
We told the user the data was ready.
A few minutes later, a message came back:
The data is wrong.
But the script worked
We checked the obvious things.
The script had executed. The records existed. The rows had been updated. There were no SQL errors.
The values in the database matched the values in the script.
Maybe something had gone wrong during execution.
So the script was executed again. This time we made absolutely sure it completed.
It did.
No errors.
We told the user to try again.
The response came back:
It's still wrong. I can't generate my report with this data.
That's when the 30-minute task stopped being a 30-minute task.
Now Development was looking at it. Production was involved. The user was waiting.
And we had one of those problems where every technical check tells you everything is working.
The database matched the script. The script had executed successfully.
Yet the result was wrong.
We were comparing the wrong things
Eventually, someone suggested something simple:
Stop comparing the database with the SQL script.
Compare the SQL script with the original Excel file.
So we did.
And that's when things started getting uncomfortable.
Imagine part of the source looked like this:
| CustomerId | Requested Status |
|---|---|
| 10482 | Pending |
| 10483 | Pending |
| 10484 | Approved |
| 10485 | Review |
| 10486 | Approved |
| 10487 | Approved |
| 10488 | Rejected |
| ... | ... |
Now compare that with the generated SQL:
UPDATE Customers SET Status='Review', UpdateDate='2026-08-10' WHERE CustomerId=10482;
UPDATE Customers SET Status='Approved', UpdateDate='2026-08-10' WHERE CustomerId=10483;
UPDATE Customers SET Status='Rejected', UpdateDate='2026-08-10' WHERE CustomerId=10484;
UPDATE Customers SET Status='Pending', UpdateDate='2026-08-10' WHERE CustomerId=10485;
UPDATE Customers SET Status='Approved', UpdateDate='2026-08-10' WHERE CustomerId=10486;
There was no obvious sequence.
Some values even matched by coincidence. But others clearly didn't.
We started comparing records one by one.
The IDs were right. The dates were right.
But the statuses weren't coming from the same rows.
At that point, the database was no longer our main suspect.
Something had happened before the SQL was generated.
So I went back to the Excel formula.
And there it was:
="UPDATE Customers SET Status='"&B22&"', UpdateDate='"&C2&"' WHERE CustomerId="&A2&";"
Look carefully:
CustomerId → A2
Status → B22
UpdateDate → C2
B22.
Not B2.
B22.
One extra character.
The customer ID came from the current row. The date came from the current row.
The status came from twenty rows below it.
And then I had dragged that formula down.
Excel had done exactly what I told it to do.
SQL Server had done exactly what I told it to do.
Twice.
For about two seconds, finding the problem felt like relief.
Then someone asked:
Okay. How much data did this script touch?
And the relief disappeared.
Finding the bug didn't fix the data
The incorrect values were already in Production.
Knowing that B22 should have been B2 didn't magically restore them.
Now we needed another script.
And after what had just happened, nobody was particularly excited to hear:
Don't worry. I'll generate another one.
We went back to the original source and generated the correction.
This time, we compared the source data against the generated SQL much more carefully. We validated samples.
Production executed the corrective script.
We checked again.
Then the user checked.
Finally, the message came back:
It's correct now. I can generate the report.
What should have been a routine 30-minute request had consumed a good part of the morning.
The user could finally continue. The data was fixed.
I thought the incident was over.
It wasn't.
Then my manager called me
He wasn't interested in whether B22 should have been B2.
We already knew that.
He asked a much more uncomfortable question:
How are we going to prevent this from happening again?
The easiest answer would have been:
I'll be more careful next time.
But that's not risk mitigation.
That's hope.
Developers make mistakes.
The real problem was that one wrong cell reference could generate an entire set of perfectly valid SQL statements and make it all the way to Production.
Excel didn't know the relationship was wrong.
The SQL parser didn't know.
The database didn't know.
Every statement was valid.
The database simply did exactly what we asked it to do.
And that was the lesson that stayed with me.
The SQL didn't fail. That was the problem.
If the formula had produced malformed SQL, this incident would probably have ended much sooner.
SQL Server would have rejected it. We would have fixed the syntax.
Done.
But this was worse.
It was valid SQL.
Using valid values.
Against valid records.
With the wrong relationships between them.
There was nothing for the database engine to complain about.
The problem wasn't Excel
I still use Excel.
And yes, you absolutely can generate SQL with Excel formulas. That's exactly what I was doing.
The problem was the workflow.
I already had structured data sitting in rows and columns, but I was manually rebuilding the relationship between those values using references like:
A2
B2
C2
One extra character was enough to break that relationship while still producing perfectly executable SQL.
Eventually, that experience became one of the reasons I built TableToSQL.
I wanted to import an Excel or CSV file, select the columns that should be updated, select the fields that belong in the WHERE, and generate the statements directly from each row.
No SQL concatenation formulas.
No dragging formulas through thousands of cells.
No A2.
No B2.
And definitely no accidental B22.
If you ever generate SQL from Excel formulas, I built TableToSQL for exactly this kind of job.
And even if you don't, there's still one thing from that morning worth remembering.
The next time you run a generated script and your database tells you:
Commands completed successfully.
Don't let that message make you too comfortable.
Because sometimes the SQL failing isn't the scary part.
Sometimes the scary part is when it works.
Top comments (2)
The important control is not “generate SQL more safely”; it is “prove the proposed state transition matches the source before granting write authority.”
For bulk corrections I prefer loading the source into a staging table with a batch ID, source-file hash, row number, typed columns, and validation status. Then:
A sample check can still miss a systematic offset that happens outside the sample. Full joins and aggregate/invariant checks are cheap compared with repairing production.
Also, rerunning the same wrong script is a revealing incident pattern. Once a user disputes a result, freeze the batch and preserve its source, generator version, reviewer, approvals, hashes, and execution receipt. That turns “commands completed successfully” from the end of validation into just one piece of evidence.
Thanks, that's an interesting approach. There's one constraint in my case that I didn't mention in the post: we don't have access to Production. We receive the Excel file, prepare the SQL script, and send it to the Production team to execute.
So I'm trying to picture the staging part in that workflow. If I have 8,000 rows, I still need to get those 8,000 rows into the staging table somehow without going back to Excel formulas.
The way I can see doing it is importing the Excel file into a table in our environment, generating the INSERTs from SQL Server, then putting those INSERTs into the final script along with the temp table and the UPDATE JOIN. Production would execute the whole script.
That would work, but it feels like a lot of extra steps for this kind of request.
Is that what you had in mind, or is there a simpler way you'd handle it when Development has no access to Production?