none
Query or Procedure to Import File with Values Used in Where Clause

    Question

  • Table name: Habitat

    HabitatNo (PK)    AnimalNo    HabitatName

    123                    10601          Jungle

    456                    11345          Desert

    789                    12333          Forest

    Imported text file: 10601 12333

    Result of query:

    HabitName

    Jungle

    Forest

    I have a tab delimited text file that will have a series of numbers. Each number needs to be used in a Where clause. Each number imported corresponds to the AnimalNo in the Habitat table.

    I either need to be able to view the results or export from a stored procedure. I know how to import the text file. How would I use the imported number in a variable in the WHERE clause? If I use a stored procedure, how would I export to an Excel spreadsheet or display the results? Should I insert the result of the query into a temp table and export that?

    I need something like this:

    IF OBJECT_ID('TEMPDB..#AnimalTemp') IS NULL
    BEGIN	
    			
    CREATE TABLE #AnimalTemp(SpeciesNo varchar(5))
    BULK INSERT #AnimalTemp
          FROM 'C:\InsertAnimalText.txt'  
                WITH (ROWTERMINATOR ='\n' 
          ,FIELDTERMINATOR = '\t' 
          ,FIRSTROW=4)    
    END
    
    Select H.HabitatName
    From Habitat H
    Where H.AnimalNo = SpeciesNo --(from text file)
    Thanks for any suggestions.


    • Edited by DavidWIII Tuesday, October 01, 2013 8:12 PM correction
    Tuesday, October 01, 2013 8:10 PM

Answers

  • If I understand what you want, then the following should work for you

    Select H.HabitatName
    From Habitat H
    Where H.AnimalNo In (Select SpeciesNo From #AnimalTemp);
    Tom

    Tuesday, October 01, 2013 8:44 PM

All replies

  • If I understand what you want, then the following should work for you

    Select H.HabitatName
    From Habitat H
    Where H.AnimalNo In (Select SpeciesNo From #AnimalTemp);
    Tom

    Tuesday, October 01, 2013 8:44 PM
  • Thanks, Tom. That's it. I won't bother describing what prevented anything from working before posting my question.
    Wednesday, October 02, 2013 6:40 PM