Take the 2-minute tour ×  I need to take some data from one
table (and expand some XML on the way) and put it in another
table. As the source table can have thousands or records which
caused a timeout I decided to do it in batches of 100 records.
The code is run on a schedule so doing it in batches works ok
for the customer. If I have say 200 records in the source
database the sproc runs very fast but if there are thousands it
takes several minutes. I'm guessing that the "TOP 100" only
takes the top 100 after it has gone through all the records. I
need to change the whole code and sproc at some point as it
doesn't scale but for now is there a quick fix to make this run
quicker ?  INSERT INTO [deviceManager].[TransactionLogStores]
SELECT TOP 100 [EventId],
[message].value('(/interface/mac)[1]', 'nvarchar(100)') AS mac,
[message].value('(/interface/device) [1]', 'nvarchar(100)') AS
device_type,     [message].value('(/interface/id) [1]',
'nvarchar(100)') AS device_id,
[message].value('substring(stri