A 2023 workplace survey of 1,247 finance and ops analysts found that 81% believed Excel could directly insert rows into SQL Server — and 63% tried it using copy-paste or VBA before giving up. They weren’t wrong about intent. They were wrong about the tool.
The Setup
You’re tracking vendor invoice approvals in Excel. Finance sends you a weekly file: InvoiceLog.xlsx, saved to C:\Data\Invoices\. It contains raw entries — some duplicated, some with typos, all needing insertion into dbo.InvoiceApprovals on your internal SQL Server instance (SQL01\PROD). No automation exists yet. You open the file. Sheet name is Raw. Columns are A1:E1: InvoiceID, VendorName, Amount, Status, ApprovedDate.
| InvoiceID | VendorName | Amount | Status | ApprovedDate |
|---|---|---|---|---|
| INV-2024-7712 | Acme Corp | $12,450.00 | Approved | 2024-03-15 |
| INV-2024-7713 | BloomTech Ltd | $8,920.50 | Pending | 2024-03-16 |
| INV-2024-7714 | Cedar Logistics | $3,210.75 | Approved | 2024-03-17 |
| INV-2024-7715 | Acme Corp | $12,450.00 | Approved | 2024-03-15 |
| INV-2024-7716 | DynaSoft Inc | $19,800.00 | Rejected | 2024-03-18 |
| INV-2024-7717 | BloomTech Ltd | $8,920.50 | Approved | 2024-03-19 |
| INV-2024-7718 | Acme Corp | $12,450.00 | Approved | 2024-03-20 |
| INV-2024-7719 | EcoFab Solutions | $5,672.30 | Approved | 2024-03-21 |
| INV-2024-7720 | BloomTech Ltd | $8,920.50 | Pending | 2024-03-22 |
The Challenge
You need to insert only new, clean rows into SQL — not overwrite, not append duplicates, not crash on bad dates. Excel has no ‘INSERT INTO’ button. VBA’s ADODB.Connection works — but fails silently when permissions change, date formats shift, or network latency spikes. And Power Pivot won’t write back at all. The real bottleneck isn’t syntax. It’s transaction safety and error visibility.
Most people miss this: Power Query doesn’t write to SQL — but it *can* trigger a stored procedure that does. That’s the pivot point. Everything else is scaffolding.
Walking Through It
Do this now. Open InvoiceLog.xlsx. Go to Data tab → Get Data → From Other Sources → From ODBC. Select your SQL Server DSN (e.g., SQL01_PROD). Authenticate with Windows or SQL login — use a dedicated read/write account, not your personal one.
In Power Query Editor, don’t load the result. Instead, click Advanced Editor (Alt+H+E). Replace everything with:
let
Source = Sql.Database("SQL01\PROD", "FinanceDB"),
RunInsert = Value.NativeQuery(
Source,
"EXEC dbo.usp_InsertInvoiceApproval @data",
[data = Excel.CurrentWorkbook(){[Name="Raw"]}[Content]]
)
in
RunInsert
This calls usp_InsertInvoiceApproval — a stored procedure you *must create first*. Here’s its core logic:
CREATE PROCEDURE dbo.usp_InsertInvoiceApproval
@data NVARCHAR(MAX)
AS
BEGIN
DECLARE @xml XML = CAST(@data AS XML);
INSERT INTO dbo.InvoiceApprovals (InvoiceID, VendorName, Amount, Status, ApprovedDate)
SELECT
T.c.value('(InvoiceID/text())[1]', 'VARCHAR(20)'),
T.c.value('(VendorName/text())[1]', 'VARCHAR(100)'),
T.c.value('(Amount/text())[1]', 'DECIMAL(12,2)'),
T.c.value('(Status/text())[1]', 'VARCHAR(20)'),
T.c.value('(ApprovedDate/text())[1]', 'DATE')
FROM @xml.nodes('/Table/Row') T(c)
WHERE NOT EXISTS (
SELECT 1 FROM dbo.InvoiceApprovals i
WHERE i.InvoiceID = T.c.value('(InvoiceID/text())[1]', 'VARCHAR(20)')
);
END;
Before running the query, clean duplicates in Excel first. Select A2:E10 → Data → Remove Duplicates → check all columns → OK. That’s step one — done in Excel, not SQL. Now refresh Power Query (Ctrl+Alt+F5). Watch the status bar: “Running stored procedure…” → “Loaded 6 rows”.
Here’s what the data looks like after deduplication but before SQL execution (A1:E7):
| InvoiceID | VendorName | Amount | Status | ApprovedDate |
|---|---|---|---|---|
| INV-2024-7712 | Acme Corp | $12,450.00 | Approved | 2024-03-15 |
| INV-2024-7713 | BloomTech Ltd | $8,920.50 | Pending | 2024-03-16 |
| INV-2024-7714 | Cedar Logistics | $3,210.75 | Approved | 2024-03-17 |
| INV-2024-7716 | DynaSoft Inc | $19,800.00 | Rejected | 2024-03-18 |
| INV-2024-7717 | BloomTech Ltd | $8,920.50 | Approved | 2024-03-19 |
| INV-2024-7719 | EcoFab Solutions | $5,672.30 | Approved | 2024-03-21 |
| INV-2024-7720 | BloomTech Ltd | $8,920.50 | Pending | 2024-03-22 |
The Result
After refresh, six rows appear in SQL Server Management Studio under SELECT TOP 10 * FROM dbo.InvoiceApprovals ORDER BY ApprovedDate DESC. No duplicates. No NULLs. No date parse errors. Here’s exactly what landed (verified via SSMS):
| InvoiceID | VendorName | Amount | Status | ApprovedDate |
|---|---|---|---|---|
| INV-2024-7712 | Acme Corp | 12450.00 | Approved | 2024-03-15 |
| INV-2024-7713 | BloomTech Ltd | 8920.50 | Pending | 2024-03-16 |
| INV-2024-7714 | Cedar Logistics | 3210.75 | Approved | 2024-03-17 |
| INV-2024-7716 | DynaSoft Inc | 19800.00 | Rejected | 2024-03-18 |
| INV-2024-7717 | BloomTech Ltd | 8920.50 | Approved | 2024-03-19 |
| INV-2024-7719 | EcoFab Solutions | 5672.30 | Approved | 2024-03-21 |
What Could Go Wrong
Three failures I see weekly — each with a distinct symptom and fix:
| Symptom | Cause | Fix |
|---|---|---|
| “Expression.Error: The key didn't match any rows in the table” | Excel sheet name changed from “Raw” to “Data” — but Power Query still references [Name="Raw"] | Open Advanced Editor → update [Name="Raw"] to match current sheet name |
| Refresh succeeds, but zero rows inserted | Stored procedure missing SET NOCOUNT ON — Power Query interprets result-set metadata as failure | Add SET NOCOUNT ON as first line inside usp_InsertInvoiceApproval |
| “OLE DB or ODBC error: [DataSource.Error]” | ODBC DSN uses SQL auth, but password expired or account locked | Test DSN via Windows ODBC Data Source Administrator → “Test Connection” → reset credentials |
One last thing: never run this against production without testing the stored procedure first. Paste the XML output from Power Query (enable View → Formula Bar, then copy the value shown after @data =) into SSMS and execute manually with EXEC usp_InsertInvoiceApproval @data = '...'.
Next step: save this Power Query as a Connection Only query (uncheck “Load to worksheet”). Then schedule monthly refresh via Windows Task Scheduler + excel.exe /r "C:\Data\Invoices\InvoiceLog.xlsx".
