Is there a way to use a parameterized SELECT script to export data via “Tools > Export Data”?

by Nov 3, 2018

We use “Tools > Export Data” heavily to export data from various databases, and for more than one-off instances we write and save a SELECT statement to retrieve the needed data. In many cases, the SELECT statement does extensive filtering (e.g., by date and organization abbreviation) and those filtering criteria can be used in multiple places within the SELECT SQL. We’d like to declare parameters with those filtering values and then reference the parameters in the SQL, but ADS doesn’t like the fact that the SQL used for the exporting the data doesn’t begin with SELECT. (We’ve encountered other situations where we’d like to export with something that doesn’t necessarily start with the SELECT keyword, but those are less common.)

For example, we’d like to have something like the following supported:

DECLARE @y INT = 2017;
DECLARE @org_abbrev VARCHAR(16) = ‘FIREFLY’;

SELECT

WHERE

AND data_year = @y
AND org_abbrev = @org_abbrev

Having to have filtering criteria like that explicitly specified rather than parameterized means we have to go rooting through sometimes elaborate SELECT’s, ensuring we find all of the particular places each such criteria is used.

If there isn’t a way to do this, perhaps the ADS devs can consider this an enhancement request?

Response

Thomas Conrad over 4 years ago
Hi Ron,

We do not have a way to do this using export in Aqua Data Studio. I have opened a feature request here to address this issue. You can monitor the progress of this issue.

Thank you, Tom