How to speed up looped INSERT INTO statements?
I really don't think table hints or BULKINSERT are going to help you here - your approach is still to process each varbinary value one at a time, and this will be your downfall regardless - especially when you discard the idea of set-based queries because you "don't think it's possible."

Here's a set-based approach with no awful loops or cursors. This assumes that the pattern is always the same (LocationID is the first byte, and CityID is the next two).

DECLARE @x TABLE(x VARBINARY(32));

INSERT @x VALUES(0x010734),(0x030735040736),(0x030742050743060712);

;WITH n(n) AS 
(
  SELECT TOP (300) (number*3)+1 
  FROM [master].dbo.spt_values -- your own Numbers table is better
  WHERE [type] = N'P' ORDER BY number
)
-- INSERT dbo.LocationCities(LocationId, CityId)
SELECT 
  x.x,      -- comment this out before insert 
  LocationID = CONVERT(INT, SUBSTRING(x.x, n, 1)),
  CityID     = CONVERT(INT, SUBSTRING(x.x, n+1, 2))
FROM @x AS x INNER JOIN n ON LEN(x) &gt; n.n;


Results:



x                        LocationID    CityID
---------------------    ----------    ------
0x010734                 1             1844
0x030735040736           3             1845
0x030735040736           4             1846
0x030742050743060712     3             1858
0x030742050743060712     5             1859
0x030742050743060712     6             1810


Some articles that will help you understand numbers tables and why generating sets in SQL Server is far superior to even the most efficient loop you can derive.


http://web.archive.org/web/20150411042510/http://web.archive.org/web/20150411042510/http://web.archive.org/web/20150411042510/http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-numbers-table.html
Why are numbers tables &quot;invaluable&quot;?
http://sqlperformance.com/generate-a-set-1
http://sqlperformance.com/generate-a-set-2
http://sqlperformance.com/generate-a-set-3