24

Version 3.0.3. It's a fairly large result-set, around 3 million rows.

Nakilon
  • 34,866
  • 14
  • 107
  • 142
alex
  • 625
  • 3
  • 7
  • 12

5 Answers5

34

Martin pretty much has this right.

The TL/DR version is that you need the "SQLScripts" plugin (which is one of the "standard' plugins), and then you can select these menu options: Session > Scripts > Store Result of SQL in File

I'm looking at version 3.4. I don't know when this feature was introduced but you may need to upgrade if you don't have and cannot install the SQLScripts plugin.

Instructions for installing a new plugin can be found at: http://squirrel-sql.sourceforge.net/user-manual/quick_start.html#plugins

But if you're performing a fresh install of Squirrel you can simply select the "SQLScripts" plugin during the installation.

Here's the long version:

  1. Run the query

    Connect to the database. Click on the SQL tab. Enter your query. Hit the run button (or Ctrl-Enter).

    You should see the first 100 rows or so in the results area in the bottom half of the pane (depending upon how you've configured the Limit Rows option).

  2. Export the full results

    Open the Session menu. Select the Scripts item (nearly at the bottom of this long menu). Select Store Result of SQL in File.

    This opens a dialog box where you can configure your export. Make sure you check Export the complete result set to get everything.

I haven't tried this with a 3 million row result set, but I have noticed that Squirrel seems to stream the data to disk (rather than reading it all into memory before writing), so I don't see any reason why it wouldn't work with an arbitrarily large file.

Note that you can export directly to a file by using Ctrl-T to invoke the tools popup and selecting sql2file.

Rod
  • 2,226
  • 22
  • 21
5

I have found a way to do this, there is a nice support for this in Squirrel. Run the SQL select (the 100 row limit will be ignored by the exporter, don't worry). Then, in the main menu, choose Session, Scripts, Store Result of SQL in File. This functionality may not be present by default, it may be present in some standard plugin (but not installed by default). I don't know which plugin though.

Martin Vysny
  • 3,088
  • 28
  • 39
3

I also wanted to export results of SQL query to CSV file using SquirrelSQL. However according to changes file it seems that this functionality is not supported even in SquirrelSql 3.3.0.

So far I was able to export only data shown in 'result table' of SQL query by right click on the table > Export to CSV. The table size is by default 100 rows and so is the CSV export. You may change the table size in Session Properties > SQL > Limit rows. E.g. change the size to 10000 and your export will also contain 10000 rows. The question is, how will SquirrelSql deal with really big result sets (millions of rows)...

Michal Vician
  • 2,486
  • 2
  • 28
  • 47
2

Run from your GUI:

COPY (SELECT * FROM some_table) TO '/some/path/some_table.csv' WITH CSV HEADER

thetaiko
  • 7,816
  • 2
  • 33
  • 49
  • 2
    Note that this will run the copy process on the *server*. So if it's a remote server, the file will be dumped on that server, and not your local machine. Likewise, you need to make sure that the server process has write permissions there. You can use \copy instead of copy to get it on the client side if you use psql - not sure if squirrel has anything like it. – Magnus Hagander Feb 23 '10 at 08:09
0

Using Squirrel 3.5.0 The "Store SQL result as file" is great if you only have a simple Select query. A more complex one with parameters wont work. Even trying to export a result of 600,000+ rows to a CSV file can also fail.

user2649863
  • 1