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

1

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 = ',')
go

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.

SELECT
product_id
, 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?

2

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

Thanks
Mangal Pardeshi
SQL Master

3

Instead of IN you can write a join query:

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

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).