I am using an Oracle/Teradata cross-connection. I want the results of the query stored in a table in Oracle. I bring up Import Wizard, pick “add sql”, and paste in the SQL. The instructions at the top of the window are a bit confusing “If opening an existing query, select the source connection used to execute the query. Results are imported into the current connection”. Well, I’m not opening a query, just pasting in the sql that the diagram generated, but anyway, the drop down box is initially populated with the Teradata connection, and since I want the query results stored into a table in Oracle, I use the drop down box to pick the Oracle source. There is a “Preview” button which I push, and the sql runs. then I go “next” and the window says “Select Target”. I pick “single new table”, and it wants a “table name” and “schema”. but the “Schma” box isn’t for entry; it is a drop down box, and for some reason only gives a choice of the Teradata database. Thus I cannot proceed, because I want Oracle, and that is not a choice. I also am wondering even if the Wizard had given me an Oracle choice whether that would have helped, because I want the results to go into a Test schema not the Production schema I pulled from (they are both in the same Oracle connection), and I am not sure if the Wizard will ask that question.
I am not sure if you can do what you want to do with just the import wizzard. However, when you build it, start out connected to Oracle since that is where you want the results to end up. Then in the import wizzard click on add query and pick the import source connection as either your Terradata or Oracle connection (not sure about this part as you are using a cross connection query). Normally the import wizzard will take data from one connection and put it into a table in the other connection. I don’t know how or if a cross connection query will work here. Let us know how it goes.
Is your query truly joining Oracle and Teradata in one query? If that is the case just connect to the destination database, ad Greg mentions, and the import wizard will understand this is a cross connection query.
If, instead you are querying Teradata and want to import into Oracle, then you need to do the following:
-
Connect to destination database (Oracle)
-
In the Import Wizard choose Add Query option.
-
Select the Teradata connection and enter your SQL.
-
Move through the rest of the Import Wizard and execute.
Thanks Debbie, it works!
Just to spell it out for others:
To generate the SQL in the first place, I connect Oracle and Teradata, and have Query Builder cross-connect do the sql.
But then I disconnect the Teradata, so that the “destination” database (Oracle) is the only connection open when I bring up Import Wizard.
And as Debbie says, the Import Wizard figures out from the sql that I want to do a cross-connect, and so connects to Teradata.
But from the fact that Oracle was the first connection opened, when I “next” to the “destination database” window to create my results table, Oracle is what is shown.
From: Debbie Peabody [mailto:bounce-Debbie_Peabody@toadworld.com]
Sent: Wednesday, November 25, 2015 4:55 PM
To: toaddatapoint@toadworld.com
Subject: RE: [Toad Data Point - Discussion Forum] Import into database table from cross-connection query
RE: Import into database table from cross-connection query
Reply by Debbie Peabody
Is your query truly joining Oracle and Teradata in one query? If that is the case just connect to the destination database, ad Greg mentions, and the import wizard will understand this is a cross connection query.
If, instead you are querying Teradata and want to import into Oracle, then you need to do the following:
-
Connect to destination database (Oracle)
-
In the Import Wizard choose Add Query option.
-
Select the Teradata connection and enter your SQL.
-
Move through the rest of the Import Wizard and execute.
To reply, please reply-all to this email.
Stop receiving emails on this subject.
Or
Unsubscribe from Toad Data Point - General notifications altogether.
Toad Data Point - Discussion Forum
Flag
this post as spam/abuse.