Make a set from CSV values in TSQL

Question

If I want to see which values from an Excel spreadsheet column don't match values in a table, I can easily create a table with Bulk Import. Is there a simpler way? EG, I want to do a query like:

select into #temp from ('a', 'b', 'c') as 'Id'
select * from #temp where Id not in (select Id from MyTable)

Answer 1

I would recommend using this UDF to parse your list into a table. Then you should be able to do the following:

SELECT * INTO #temp FROM dbo.udf_List2Table( 'a,b,c', ',') 
SELECT * FROM #temp WHERE item NOT IN (SELECT Id FROM MyTable)

Answer 2

Also, I've been using the additional column in Excel with bunch of CONCATENATE.TEXT commands to create the INSERT statements. This column alone, copied into the text file, is your script!