Get value from file

Question

Is it possible to do a query with a where clause that matches against a list of values from a .csv file ?

Something like:

 Select thingA * from 
 table1 where thingB exists in csv file

Answer 1

Using the technique described in Use SPLIT_STRING with OPENROWSET:

Assume that my text file has 3 rows with values (one, two and three)

--Setup demo data
DECLARE @T table (ID int, ThingB varchar(10))
INSERT INTO @T (ID,ThingB) VALUES 
(1,'one'),(4,'four')

--Declare variables
DECLARE @CrLf CHAR(2) = CHAR(13) + CHAR(10)
DECLARE @BulkColumn VARCHAR(max)

--Replace CR/LF with tilde (because tilda is not in my txt file)
SELECT @BulkColumn = replace(BulkColumn, @CrLf, '~')
FROM OPENROWSET(BULK 'C:\Temp\z.txt', SINGLE_CLOB) MyFile

--Select id from @T table where ThingB exists in the values
--from the text file
SELECT ID
FROM @T t
WHERE EXISTS (
        SELECT *
        FROM string_split(@BulkColumn, '~')
        WHERE value = t.ThingB
        )

---

| ID |
| 1  |