INSERT INTO tLocalFileIDs SELECT .... behaviour

[David Stevenson]

I have a piece of code which uses this syntax to select the contents from one table into table tLocalFileIDs, which is empty initially.

However, when the source table contains a lot of entries, the INSERT command seems to return BEFORE the insert operation is complete, so that when the next piece of code runs, which uses the data from the table tLocalFileIDs, the table is still empty. For example, the datareader shows zero rows:

cmdFiles.CommandText = "SELECT LocalFilePath FROM tLocalFileIDs"
cmdFiles.FetchAll = True
drdFilereader = cmdFiles.ExecuteReader
Dim filepaths(drdFilereader.RecordCount - 1) As String
drdFilereader.Seek(0)
Dim i As Integer
While drdFilereader.Read()

If I then retry shortly after, the data has appeared.

Is this as a result of the non-transactional nature of myISAM, that it returns before the operation is complete? Any suggestions or work-arounds would be appreciated.

Many thanks
David Stevenson