querying specific rows by list

  • Hi, i have a program that returns a list of sites with siteIDs in excell/csv format. For each site id there is a record in my database table.What is the best way to query my table to only return these sites and make it into a view. Im guessing a select statement where SiteID = x1,x2,x3,x4,... is not the most efficient fastest way. Please help. Thanks!

    -Nick

  • nicholasferri (7/8/2013)


    Hi, i have a program that returns a list of sites with siteIDs in excell/csv format. For each site id there is a record in my database table.What is the best way to query my table to only return these sites and make it into a view. Im guessing a select statement where SiteID = x1,x2,x3,x4,... is not the most efficient fastest way. Please help. Thanks!

    -Nick

    A select statement where SiteID = x1,x2,x3 will not quite work. If I understand you correctly your program returns a delimited string? (i.e. 'x1,x2,x3')

    You might want to take a look at the link in my signature about splitting strings.

    Not really sure what you mean about turning it into a view though.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • sorry, let me explain it again. I have a excell file of SiteIDs

    Row Site

    1. 202

    2. 203

    3. 204

    I want to take this excell and run a query on on of my tables to return the rows for these sites

    Site Name Address

    202 Mcdonalds 11

    203 Wendys 321

    204 BK 3213

    how would i encorporate this excell of SiteIDs to query my table for these specific rows.

  • Import the excel data into a table then write a query joining your primary site table and the data you imported on the SiteID field.

    SELECT

    *

    FROM Sites S

    INNER JOIN ImportedSites IS

    ON S.SiteID = IS.SiteID

    There may be a way to join against the excel file without importing but I'm forgetting what the technique is called.

  • Assuming your Excel sheet is accessible from the SQL Server, you can use Chrissy's INNER JOIN on an OPENROWSET to read directly from the Excel sheet.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • Thanks. That took care of it 🙂

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply