1

I have a database with over 50 Million records. I need to query roughly 2000 specific but unrelated records.

Is there a way to do this without having to type each unique key? i.e. a loop reading them from an external file? I'm no SQL expert so my thinking is from a programming perspective.

Responding to questions below: They are unique records. I'm using Oracle. Right now this is a one time thing, but it could become a regular occurrence.

I had come up with the idea of using vi to edit a text file and do 2000 "select ID from table" is a script. However I wanted something less "brute force".

user3293934
  • 11
  • 2
  • SQL Server `SELECT TOP 2000` to get "random" 2000 records, note that "random" is not really random. –  Feb 10 '14 at 17:31
  • 1
    possible duplicate of [How to request a random row in SQL?](http://stackoverflow.com/questions/19412/how-to-request-a-random-row-in-sql) – Artyom Neustroev Feb 10 '14 at 17:32
  • 1
    You need to give us much more info on *which* 2000 records you want. You have their keys, and you want to access *those* specific records? Or you just want any given 2000? Where did the 2000 IDs come from? Can you reproduce the same set of records with a specific query? – meager Feb 10 '14 at 17:32
  • @ArtyomNeustroev I have a strong suspicion that this isn't about getting random records. He has 2000 record IDs, and he wants to know how to get them without typing those 2000 IDs in. – meager Feb 10 '14 at 17:32
  • 1
    @meagar Probably. If he *does* want random records, this works in MSSQL: `SELECT * FROM BigTable ORDER BY NEWID()` – Jerad Rose Feb 10 '14 at 17:33
  • I'm going to go ahead and edit out the "random unrelated" bit to prevent further close votes, as it seems obvious on second reading that you're trying to look up 2000 specific records based on IDs stored in an external file. If that isn't correct, feel free to rollback my change. – meager Feb 10 '14 at 17:36
  • @meagar I think that changed the meaning of the question. Think this is a duplicate question tho as Artyom noted. – MikeSmithDev Feb 10 '14 at 17:41
  • @MikeSmithDev I don't think that's near certain, and it's safer to assume that my interoperation is correct as that leaves the question open where assuming it's a duplicate slams the question shut. There is no sense speculating until OP chimes in. – meager Feb 10 '14 at 17:47
  • Also which DBMS are you using? Postgres? Oracle? – a_horse_with_no_name Feb 10 '14 at 20:33

1 Answers1

0

Is this something you do regularly, or is this a one-time occurrence? If it's a one-time thing, I'd simply prepare a long query in vim by joining all the IDs with commas, and inserting them into an "in" clause:

select * from my_table where id in (1, 2, 3, 4, ..., 1998, 1999, 2000)

This depends on your database being able to handle relatively long queries.

meager
  • 209,754
  • 38
  • 307
  • 315