Toad World® Forums

Query Result Export


#1

Is it possible to export the result of a query directly into a new database table ?

Thanks.


#2

Unfortunately no, you cannot export the result of a query directly into a new database table from our Query Builder. Here are two workarounds:

  1. You can send the text of the query to a SQL Editor window. In the Query Builder window, click on the “Generated Query” tab and from the shortcut menu (right-click menu) choose “Open in Editor…”

Inside the SQL Editor window, manually type in an
insert into table_name (column1, column2, column3 …) in front of the select statement, and then execute the INSERT … SELECT … statement

  1. In the Query Builder window, click the “Run Query” button at the bottom of the window. You will be brought to the Result Sets tab showing your Query Results. From the Result Sets shortcut menu (right-click menu) choose “Export to Excel->Excel file->Excel file…” and save the query results in an excel file. (If you can’t see that menu, make sure that you are right-clicking on one of the rows in the result set, not in an empty part of the Result Sets pane. The Result Sets panewill have an “empty” part if there aren’t enough rows returned to fill up the Result Sets pane.) You can then use the Data Import wizard (main menu item Tools->Import Wizard…) to import the excel file into a table.

Please let us know if this works for you.


#3

I thought this would be a good feature when we were first designing Toad for Data Analysis. But we did not add it because it did not seem to be something needed by our Beta users and test cases.
But since you have made this request I have now added this enhancement as CR0224714. We have all the tools to do this so we can add to version 2.0.

There is one feature that is added that I think is pretty cool. When importing we now offer creating a new table for the data on the fly. You do not have to make the extra step of creating the table. It is a simple table of just vchars. But I find many use cases where this is quite handy.

Debbie


#4

Also on some database you can simply prepend the query (If you send it to the editor) with:

create table {tablename} as {select statement}

This will create the table and insert the result of the select into it.


#5

Create table NewTable as select * from existingTable