We have a batch import process that BULK INSERTs raw data into staging tables, does some data mapping, and then pushes it to another database for validation.
The procedure where my issue occurs is very long and is filled with column names so I'll give you the run down:
Raw files are BULK INSERTED into various Import Tables. If this succeeds, those records begin migration to the validation database.
New GUIDs are generated for the Validation Database (@ValBatchId)
Basic Record information is put into a Records table
INSERT INTO [ValidationDatabase].[dbo].[RecordsTable]([RecordId],[BatchId],[Columns...])
SELECT NewId(), @ValBatchId, Columns...
- Import Tables have their data mapped from provided values to standard values and are inserted into their respective tables in the Validation database
While performing the last step of the process, it will occasionally silently fail and insert 0 records into a specific table:
INSERT INTO [ValidationDatabase].[dbo].[Table] ([Columns]...) ( SELECT rec.RecordId, imp.stuff... FROM [ValidationDatabase].[dbo].[RecordsTable] as rec INNER JOIN [ImportDatabase].[dbo].[ImportTable] as imp ON... WHERE rec.BatchId = @ValBatchId AND imp.BatchId = @ImportBatchId )
I added some diagnostic code in the stored procedure to makes sure this was a SQL Server error and not a disk issue by adding
@@ROWCOUNT into a logging table:
DECLARE @RowsInserted INT = @@ROWCOUNT INSERT INTO [ImportDatabase].[dbo].[MigrateLog]([BatchID],[TransactionDate],[Description]) VALUES(@ImportBatchId,GETDATE(), CAST(@RowsInserted AS VARCHAR(MAX)) + ' records inserted into <table name>')
...where it displayed this for a particular batch:
inserting into <table name>
0 records inserted into <table name>
Here's the kicker: Immediately after the rest of the migration process finishes, if I go and rerun the select statement inside the insert...and it returns the data that should have been inserted. Most disturbingly, If I go and re-import that batch of data (as a completely separate batch, without any reference to the failed batch) it will work without issue.
I can't for the life of me figure out what is going on. It has to be the SELECT statement inside the INSERT that isn't returning records but I can't recreate this problem, even with the same batch it happens on. Is this a bug in SQL Server, or am I missing something here?
SQL Server 2016 Enterprise
Both databases are on the same server, no remote queries or linked servers
READ COMMITTED Isolation Level
Snapshotting is off