#Temporary table in query not working

• TJ_T

  Old Hand

  Points: 352

  More actions

  June 14, 2019 at 10:12 pm

  #3654006

  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

   

• Site Owners

  SSC Guru

  Points: 80342

  More actions

  June 15, 2019 at 11:10 pm

  #3654152

  Thanks for posting your issue and hopefully someone will answer soon.

  This is an automated bump to increase visibility of your question.

• pietlinden

  SSC Guru

  Points: 63589

  More actions

  June 16, 2019 at 12:59 am

  #3654177

  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

• Lowell

  SSC Guru

  Points: 323527

  More actions

  June 16, 2019 at 1:14 am

  #3654183

  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

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• TJ_T

  Old Hand

  Points: 352

  More actions

  June 16, 2019 at 2:09 pm

  #3654250

  Thanks, it works now