SQLTeam.com | Weblogs | Forums

SQL Query Not Recognising Full List - What Am I Missing?


I have a list of IDs in a CSV file (portal.txt) and which I am using within a SQL Query.

So first I am creating a temporary table as follows:

create table #tmptable2
Product_id varchar(30)

Then populating the table as follows:

Bulk insert #tmptable2
from 'C:\Chris\portal.txt'
with (FieldTerminator=',', RowTerminator = ',')

So if I now run Select Product_id from #tmptable2 it returns the 3 lines in portal.txt. All good so far. But when I run the following query I only get 1 of the 3 lines returned.

, product_desc
FROM cm_products
WHERE product_id in (SELECT product_id FROM #tmptable2)

Must be missing something but can't see what it is. Can anyone pont me in the right direction please?


Are there any NULL values in product_id column in #tmptable2?

Mangal Pardeshi
SQL Master


Instead of IN you can write a join query:

    , c.product_desc
FROM cm_products C
INNER JOIN #tmptable2 T
on c.product_id = t.product_id


That should work. You must have something else going on. Check for special characters in the product_id column (such as carriage return, line feed or tab).