July 8, 2013 at 3:15 pm
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
July 8, 2013 at 3:27 pm
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/
July 8, 2013 at 3:33 pm
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.
July 8, 2013 at 4:02 pm
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.
July 8, 2013 at 6:35 pm
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 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
July 9, 2013 at 7:32 am
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