I ran a script that fetched me 8million records after running for 2 hours. I need to save this result so that I can pull it into MS Access. Smaller size reults were easy, as I would save into Excel adn then import that file into Access.
How do I handle this? I tried Toad fo Analysts and Toad for Oracle.
There’s a feature in Toad for Data Analysts which I think will really
help. Take your query and use it in the Export Wizard (Tools >> Export
Export Wizard). Once you have the wizard open, select the
“Query” export category and paste the query in the edit window (or
you can browse to the saved query on disk). Then, save the output as a delimited
format (to avoid any MS Excel row limitations). Once you have any remaining
options set, you can either run it immediately or save it as a template –
this will let you kick it off as a job at any time or schedule it to run at a
specific time. Once this is done, you can use the Toad for Data Analysts import
wizard to import the data into MS Access.
Doing it this way is really helpful because it bypasses loading the 8 million
records into the data grid UI and just writes them straight to disk. The result
is much faster performance with less system memory used. You would only be
limited at this point by the space you have on your hard drive.
In a future release, we’re looking into letting you suck that data into an
internal Toad for Data Analysts database directly instead of having to use MS
Access. It may help you save some time instead of having to export first and
then import into MS Access for further analysis.
Quest Software, Inc.
Product Manager, Database Development Products
If you’re running the query in the Editor, you can right-click on the data
grid and Export Dataset directly to an Access Database File.
Although if your purpose is to move 8 million rows from Oracle into Access, Toad
might not be the best tool. In Access you can create a Linked Table or a Query
that goes over the ODBC link to your Oracle database and do a direct Make Table
query in Access to pull the data in.
IT Commercial Technical Services - Data Management Team
800 N. Lindbergh Blvd. G3WI - Saint Louis, MO - 63167
R-click, save as/export should do the trick.
One more thing – I forgot. You can save the export as an Access file in
Toad for Data Analysts, bypassing the import step.
Why would you want 8M records in Access? Maybe there’s another way to
solve your problem?
The direct load is also available in Toad for Oracle, disable the ‘Display
All Results in Grid’ option.
To export that much data in Toad, enter your query in the editor, run with F9
(NOT F5!), right-click, choose “Export Dataset”, and make sure that “Display
results in grid” is UNchecked.
I don't wish to be rude about Access, but...
Why would you want 8M records in Access? Maybe there's
another way to solve your problem?
Can Access actually handle 8 million rows? In one table? In one
Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk
Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.
We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.
If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk
Can Access actually handle 8 million rows?
In one table? In one database?
I must admit, with the limitations inherent in the rest of MS’
products… it does make one rather curious what the limitations of Access
I don’t believe it has a row limit but it does have a size limit (at least the
older versions did) of 2Gb. Maybe ribbons allow it to hold more data.
and i thought i was pushing the envelope!
i have oracle running on my laptop where I am able to handle multi-terabytes of
you can configure Oracle to run on your laptop
would be interesting to see it run on a blackberry tho’
Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité
Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger
sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung
oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem
Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung.
Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung
fuer den Inhalt uebernehmen.
Ce message est confidentiel et peut être privilégié. Si vous n’êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l’expéditeur. N’importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l’information seulement et n’aura pas n’importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni.
Dan, i am trying out your suggestion. I put ina filter to reduce the utput o 1million… made sure the code works and put it into the export option as you explained. It errored out after 20min. giving some weird reason about a table alias not being correct. But I made sure it is right… now I am not sure why this is happening…
I had a colleague who works only on Access check some forums and yes, Access 2007 can handle 8million records. I personally think it will be a resource hog and will crash my machine. But before i get to that point, i need to make sure my 8M records get into Access from Toad…
I will update you all… Thx.
Hmm… let’s take a look. Can you send me the entire contents of what
appears on the Export result window, accessible through “View Event
Log”? You can send it to me offline if you want at Daniel [dot] Norwood
[at] quest [dot] com.