Run SQL query to retrieve 650 unique records

Question

Run SQL query to retrieve 650 unique records

SSRS Newbie

Hall of Fame

Points: 3244
More actions
May 14, 2015 at 9:00 am

#298151

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.

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

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

cunningham SSCarpal Tunnel Points: 4595 More actions · Answer 1

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.

SSRS Newbie Hall of Fame Points: 3244 More actions · Answer 2

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?

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 3

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

SQLBill SSC Guru Points: 51440 More actions · Answer 4

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

cunningham SSCarpal Tunnel Points: 4595 More actions · Answer 5

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

cunningham SSCarpal Tunnel Points: 4595 More actions · Answer 6

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

Jeff Moden SSC Guru Points: 1004301 More actions · Answer 7

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)

Xedni SSCertifiable Points: 6951 More actions · Answer 8

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]

Dwain Camps SSC Guru Points: 86948 More actions · Answer 9

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

Jason A. Long SSC-Insane Points: 23786 More actions · Answer 10

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