Nice article Amit. Will I use CTE for converting delimited lists into tables? Maybe not, but it is always fun to challenge yourself with new and interesting ways to solve old problems. On that note, the following is something i was playing with after reading your article.

DECLARE @string nvarchar(max)

DECLARE @delimiter char(1)

SET @delimiter = ','

SET @string = '3903044,7569634,3947703,4397641,8304414,3889301,504543,5543592,2468890,644965,3343770,5383570'

SET @string = 'SELECT ''' + replace(@string,@delimiter, ''' [Item] UNION ALL SELECT ''') + ''''

EXEC sp_executesql @string

It is not how I would solve the problem, but is yet another fun and imaginative method that does not use loops and is ridiculously simple.... however, it failed due to memory issues with the 10,000 item list...maybe as a collaborative effort we can find a way to make this work with 10,000 or more items...

...of course if i had a list of 10,000 items, i would probably do something like this..which is VERY fast...if only we could pass a variable to BULK INSERT instead of a filepath 🙂

CREATE TABLE #TEMP (item varchar(20))

BULK INSERT #TEMP

FROM 'c:\shared\delimitedlist.txt'

WITH (ROWTERMINATOR = ',', FIELDTERMINATOR = ',')

SELECT * FROM #TEMP

DROP TABLE #TEMP

"VERY FAST" = 18 ms. to write to the #temp table and 185 to select from it...

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:

CPU time = 15 ms, elapsed time = 18 ms.

(10000 row(s) affected)

(10000 row(s) affected)

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 185 ms.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 1 ms.

-Jeremy