Post reply

#Temporary table in query not working

  • 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

     

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

  • 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

  • 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!

  • June 16, 2019 at 2:09 pm

    #3654250

    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