Is it possible? I’m trying to get an 850,000 record Access table into an Oracle schema, with CREATE TABLE AS from the Access connection. Query Builder was able to write the highly idiosyncratic cross-connection SQL that TDA likes, and the query is running, but I’m getting this error:
Cross Query Database Error
ERROR on line 1, col 14, offset 13: TDA3 ONLY extensions found, not supported due to appropriate QP5 language extension not set
Is this user error, a bug, or an installation issue? I’m running Toad for Data Analysts 3.1.0.638 in Windows 7.
Basically, connect to the database you want to import to. (Oracle) Then choose the Add Query Import type. Change the connection to Access and enter your query. At the end of the wizard you can enter an existing table or create a new one and even change the column data types and set an index. This connects to Accsess, retrieves the data and imports into Oracle. Save this as a template and use in automation if you want to repeat this process.
I have successfully used the Toad Data Point 3.2 import wizard in the last few months to transfer many millions of data rows to Oracle 11gR1 from SQL Server 2005, and recently, 2 million rows from an MS Access 2010 database. The import wizard generally works quite well for me.
Thanks Debbie, that worked, total time 8 minutes as opposed to the Oracle SQL Developer “Copy to Oracle” function which took 7 hours.
If anyone is reading this later, the exact steps are:
in Toad, connect to the destination Oracle schema, Debbie said “Basically, connect to the database you want to import to.”ß important – I recommend a Toad right click and Reconnect to your destination Oracle schema
then in TDA (all these steps probably identical in Toad Data Point) Tools > Import > Import Wizard > Next > Add Query
there’s a click and drop down where you choose the source connection, this was Debbie’s verbiage “Change the connection to Access and enter your query”
in the same dialog, click in the white space and type your query, something like SELECT * FROM <table> (it’s not a create table as query, Toad is handling that)
click Next
now you have the Select Target dialog, TDA knows your destination from step 1
in the Select Target dialog, Debbie covered this next part “At the end of the wizard you can enter an existing table or create a new one and even change the column data types and set an index.” I can add that TDA makes generally competent guesses on datatypes, but it’s based on a limited scan, and you will probably want to input your own
click Next, Next, and Finish
sit there and wish TDA had a progress bar, a cute little moving graphic, anything except a blank gray screen while it imports or exports