Friday, 23 October 2009

Quick Export Query Results

When you run a query in the SQL Worksheet using "Run Statement" a Data Grid is used to display the results.

If you then want to export these results youll find that the query is run again.
This can be a pain if it is a long running query.
The following may help.

1) The Data Grid is only populated with 50 rows (by default) at a time. This is for performance reasons, but if you want to use CTRL C, to copy the results it will only copy the results which are currently present.

So click on the data grid and do
CTRL END
This will populate the data grid with the entire result set
. When you perform a select all (CTRL A) and copy (CTRL C) you will get the entire result set, but a TAB is used as the delimiter which may not be good enough.

2) Create a VIEW from the query and export from the VIEW
CREATE OR REPLACE quickExportView AS

Then in the navigator right click on the view and perform Export Data.
In this way you dont have to run the query twice.


Things to Note
1) When using the export, it will always wrap your original query as a subquery.
SELECT * FROM ()

It does this so it can perform column and row filtering.

2) For a future release export will be based on the data grid so that the query does not need to be rerun

3) Use "Run Statement" (F9) only for queries. It does support DDL and DML, but these are best executed using "Run Script"(F5)