Will a Cross Apply bail, but not error in SQL Server?

Question

We're inserting a couple millions records into a details table, the data for which is stored in the master table in CSV type format. To do the insert we're using a cross apply function, which seems to work if I do the inserts one by one, but if I do the entire insert, only about 2/3 of the records go in, and the query just stops, with success after about 3 and half hours.

Here's the insert query

   INSERT INTO DetailsTable(MasterTableID, DetailColumn1, DetailColumn2) 
   SELECT MasterTableID, DetailColumn1, DetailColumn2 FROM MasterTable
   CROSS APPLY [fn_CreateDetailData](MasterTableID, '§') 

If I do the following for one of the records that doesn't get inserted, everthing will work fine and there record is inserted just fine

   INSERT INTO DetailsTable(MasterTableID, DetailColumn1, DetailColumn2) 
   SELECT MasterTableID, DetailColumn1, DetailColumn2 FROM MasterTable
   CROSS APPLY [fn_CreateDetailData](MasterTableID, '§') 
   WHERE MasterTableID = 12345

From everything I've ever known about SQL Server, a single insert statement exists in one transaction, so its either all or nothing, but that doesn't seem to be the case here.

Answer 1

Is this exactly your code, for the SELECT? Have you ommitted any detail like, say, use of NOLOCK hints? NOLOCK is notorious for causing misterious gaps in data, see Previously committed rows might be missed if NOLOCK hint is used