I have a query that takes one record from #itemtemp
, and locates all entries from ReportCSharp
that match, inserting those matches into the #link_table
. At present, the query runs in 7.5 min, which appears slow for iterating over only 1458 records in the #itemtemp
table.
DECLARE
@num int
, @path varchar(100)
, @output varchar(100)
, @max int
SET @num = 1
SET @max = (SELECT max(num) FROM #itemtemp)
WHILE @num < @max
BEGIN
SET @path = (SELECT path from #itemtemp where num = @num)
INSERT INTO #link_table
SELECT
itemId
, Path
, @path
FROM ReportCsharp
WHERE Script like '%"' + @path + '"%'
SET @num += 1
END
How can I remove the WHILE
loop and replace with more set based operations?