I have a comma separated list with a lot of values(strings) and I want to compare it to a MS SQL Server 2008 table. I am trying to get which of the values in the list that are not in the table. I don't want to write to the database at all if possible.
I have found this previous post that is similar to my question but I can't quite figure out how to make it work with strings.
Comparing list of values against table
Any suggestions?
To follow up on this. The closest thing I could find to a solution is to manually enter the comma separated values by first formatting all rows then running it something like this:
DECLARE @TempTable table(firstcol varchar(50))
INSERT INTO @TempTable VALUES ('mystring1')
INSERT INTO @TempTable VALUES ('mystring2')
INSERT INTO @TempTable VALUES ('mystring3')
INSERT INTO @TempTable VALUES ('mystring4')
...
SELECT * FROM @TempTable WHERE firstcol NOT IN (SELECT tablecol FROM [MyTable])
It seems that if I have a greater version (2012?) I would also be able to do this:
INSERT INTO @TempTable VALUES('mystring1'),('mystring2'),('mystring3')
The closest other alternatives I could find though still writing to database would be:
sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO
select TerritoryID
,TotalSales
,TotalCost
INTO CSVImportTable
from openrowset('MSDASQL'
,'Driver={Microsoft Access Text Driver (*.txt, *.csv)}'
,'select * from C:\csvtest.CSV')
Found here: How to create and populate a table in a single step as part of a CSV import operation?
And this alternative with a temporary table:
CREATE TABLE #TempTable (FName nvarchar(max),SName nvarchar(max),
Email nvarchar(max));
BULK INSERT #TempTable
FROM 'C:\52BB30AD694A62A03E.csv'
WITH (FIELDTERMINATOR = ',',ROWTERMINATOR = '\n')
Found here: Whats wrong with this SQL statement for table variable bulk insert