When one spreadsheet feeds several related SQL Server tables, load its rows into a flat staging table first. Validate the batch there, then run an explicit stored procedure that inserts parents before children in one transaction. This keeps file parsing separate from relational data rules.
Last updated: October 4, 2026.
BEGIN TRANSACTION;
INSERT dbo.Category (CategoryName)
SELECT DISTINCT s.CategoryName
FROM dbo.ProductImportStage AS s
WHERE s.BatchId = @BatchId
AND NOT EXISTS (
SELECT 1
FROM dbo.Category AS c
WHERE c.CategoryName = s.CategoryName
);
INSERT dbo.Product (SKU, ProductName, CategoryId)
SELECT s.SKU, s.ProductName, c.CategoryId
FROM dbo.ProductImportStage AS s
JOIN dbo.Category AS c
ON c.CategoryName = s.CategoryName
WHERE s.BatchId = @BatchId;
COMMIT TRANSACTION;A batch identifier isolates one upload from another. The parent insert creates missing categories first; the child insert then resolves each category through a join. Add a unique constraint on Product.SKU and the appropriate business key on Category so accidental reruns cannot silently create duplicate records.
Validate before changing production tables
Store the original row number with every staged row. Before the transaction starts, reject missing SKUs, invalid lengths, duplicate business keys, and references that cannot be resolved. Return those failures as an error table that identifies the spreadsheet row and reason. Do not partially import a batch that is expected to be atomic.
Microsoft’s data-loading pattern guidance recommends bulk loading into staging, validating there, and then using set-based INSERT ... SELECT statements in a transaction. The same database pattern applies regardless of which library reads Excel.
Call the import procedure explicitly
Let the application load the stage and then call a stored procedure with @BatchId. Avoid using a trigger as the workflow coordinator: bulk loads can fire it for many rows at once, error reporting becomes indirect, and retry behavior is harder to control. An explicit procedure gives the caller one result and one transaction boundary.
The SQL Server INSERT reference documents the set-based forms used here. For complementary patterns, review SQL INSERT, SQL joins, and stored procedure output parameters.
