CTAS from Access table into Oracle schema (?)

Toad Data Point & Toad Intelligence Central
1

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.

thanks!

2

The best way to do this is to use the Import Wizard. There is a cross connection aspect to the wizard. This feature is discussed in this Blog video --> http://toadworld.com/Blogs/tabid/67/EntryId/703/New-IMPORT-Features-for-Toad-for-Data-Analysts-2-7.aspx

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.

Debbie

3

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.

4

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:

  1. 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

  2. then in TDA (all these steps probably identical in Toad Data Point) Tools > Import > Import Wizard > Next > Add Query

  3. 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”

  4. 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)

  5. click Next

  6. now you have the Select Target dialog, TDA knows your destination from step 1

  7. 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

  8. click Next, Next, and Finish

  9. 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
5

Glad to hear this worked. We do have a progress bar. Open up the Background processes docked window. It will tell you progress.

Debbie

6

Will this process work if we are JOINing2 tables from different schema …using JOIN statement in Cross-Connection Query builder