June 14, 2019 at 10:12 pm
I have a query that is not working and could use some help. It doesn't exclude the software littles from the temporary table. The create #tempTable works and I can select the titles from it, but the titles still appear when I run the query below.
CREATE TABLE #TempTable (Software NVARCHAR(MAX));
BULK INSERT #TempTable
FROM 'c:\temp\MyExcludedSoftware_LIST.txt'
WITH (
DATAFILETYPE= 'widechar',
FIELDTERMINATOR = '\t',
ROWTERMINATOR = '\n',
KEEPNULLS
);
SELECT SoftwareTitle
FROM SoftDB
WHERE
SoftwareTitle NOT IN
(
SELECT *
FROM #TempTable
)
DROP TABLE #TempTable
+++++++++++++++++++++++++++ Text file +++++++++++++++++++
Microsoft Visual C++ 2010 x64 Redistributable - 10.0.40219
Microsoft Visual C++ 2010 x86 Redistributable - 10.0.40219
Microsoft Visual C++ 2012 Redistributable (x86) - 11.0.61030
Microsoft Visual C++ 2012 x86 Additional Runtime - 11.0.61030
Microsoft Visual C++ 2012 x86 Minimum Runtime - 11.0.61030
Microsoft Visual C++ 2013 Redistributable (x64) - 12.0.21005
Microsoft Visual C++ 2013 Redistributable (x64) - 12.0.30501
Microsoft Visual C++ 2013 Redistributable (x64) - 12.0.40660
Microsoft Visual C++ 2013 Redistributable (x86) - 12.0.21005
Microsoft Visual C++ 2013 Redistributable (x86) - 12.0.30501
Microsoft Visual C++ 2013 Redistributable (x86) - 12.0.40660
Microsoft Visual C++ 2013 x64 Additional Runtime - 12.0.40660
Microsoft Visual C++ 2013 x64 Debug Runtime - 12.0.21005
June 15, 2019 at 11:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
June 16, 2019 at 12:59 am
Shouldn't your WHERE clause be like this?
WHERE SoftwareTitle NOT IN ( SELECT <fieldname> FROM #TempTable)
You can't match a single column (SoftwareTitle) to all the columns (*) in #TempTable
June 16, 2019 at 1:14 am
NOT IN doesn't work the way you might expect if the column tested has any NULL values.
you'll want to exclude them explicitly.
WHERE SoftwareTitle NOT IN
(SELECT Software FROM #TempTable WHERE Software IS NOT NULL )
Lowell
June 16, 2019 at 2:09 pm
Thanks, it works now
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply