Run SQL query to retrieve 650 unique records

  • Hello All,

    I've a excel spreadsheet with 650 records with unique PONumbers. I need to pull data from SQL server based on the PONumbers. I don't want to run select statement 650 times. How do I retrieve the records in efficient way? Any help is appreciated. Thanks.

  • why don't you import the excel spreadsheet into sql, then you can write and execute a query referencing the two tables with a join.

  • cunningham- Thanks for your response. I've never done the way you suggested. Seems like the best way to get the data. Can you give me a hand on this?

  • SSRS Newbie (5/14/2015)


    Hello All,

    I've a excel spreadsheet with 650 records with unique PONumbers. I need to pull data from SQL server based on the PONumbers. I don't want to run select statement 650 times. How do I retrieve the records in efficient way? Any help is appreciated. Thanks.

    just for clarification....cos I am not sure I fully understand (not unusual :-D)

    it appears in the way you have worded your question that you want pull records from SQL into Excel??...is this correct......or are you trying to pull from Excel to SQL. ???

    perhaps some example sql/excel would help my tired brain 😛

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • This will get you started....

    Using SSMS, find the database you want to import the spreadsheet into. Right click on the database and choose: Tasks>Import Data. Follow the wizard and it will guide you through what needs done to import the data. Then you can write the query you need by joining the two tables.

    -SQLBill

  • hi you should have 'import and export data' tool installed on your machine if you installed management studio complete. you will find it under start > programs > sql 2008(r2) (depending on your version). fire that tool up, it's quite good and easy to use, you just need to choose the source of the data (your spreadsheet), the destination (your new sql table, the tool will create this). see if you have the tool installed fire it up and give it a go.

    post back if you get stuck or have any questions

  • sqlbill's method is a bit of a quicker route, which i'll probably use in future! but effectively uses the same tool.

  • If this is something that needs to be done more than once or automated, post back and we'll turn it into a stored procedure for you. 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • I completely agree that the quick easy solution is to use the import export wizard.

    I'll just offer one more ad-hoc way to do this. Since your data is pretty small, you can also create an additional column in your Excel spreadsheet which is a computed field of the values you care about and that basically scripts out the inserts.

    so for example

    PONumber Date Statement

    1 02-20-84 ="select "&A1&",'"&B1&"' union all"

    and you end up with

    select 1, '02-20-84' union all

    It's not always easier to do it this way, but sometimes I use this method for ad-hoc things like this.

    Executive Junior Cowboy Developer, Esq.[/url]

  • I do this all the time.

    1. Create a column in your spreadsheet where you will build the SQL you need.

    2. Enter the cell formula shown in B2 (above)

    3. Copy that formula to B3 and add UNION ALL before SELECT in the CONCATENATE string.

    4. Copy cell B3 down to B4:B5

    5. Now copy cells B2:B5 to the clipboard.

    Go to SSMS and create your code like this:

    WITH MyPOs (PONumber) AS

    (

    -- Paste clipboard result here

    SELECT 'A001'

    UNION ALL SELECT 'A009'

    UNION ALL SELECT 'A011'

    UNION ALL SELECT 'A52'

    )

    SELECT *

    FROM POTable a

    JOIN MyPOs b ON a.PONumber = b.PONumber;


    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

  • If the PO Numbers are all the same length, the "Shift + Alt" block typing trick works well too...

Viewing 11 posts - 1 through 10 (of 10 total)

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