SqlDbx Forum

SqlDbx

You are not logged in.

#1 2016-10-12 15:04:55

geolemon
Member

Run query to file? Run query to external sandbox?

I work for a major player in healthcare insurance.   Currently, our developers, analysts, and data QA use a tool called WinSQL.
We're looking for a replacement for it as the newer versions have been perceived as unstable, crashing increasingly frequently.
 
We have many very large databases hosted on many platforms.  Analysts and QA have access to sandbox databases, those are typically SQL Server, where they are DBO - they use this to load data from other databases, to merge (or in QA, compare) data and analyze it in that neutral environment.

WinSQL is used because associates can develop their queries, and then "execute to file" or "execute to external database".
It's important to note that that exporting to file does not require prior execution of the query, as they may return millions of rows, which can be prohibitive both in terms of time and utilizing memory on that client system. 

WinSQL is good in this regard, as that option allows the user to specify file options, execute, and it displays the growing rowcount as the file is 1) created and 2) populated.
Similarly, if executing to external database, it is pretty slick in that it analyzes the underlying data and generates (and executes) a "CREATE TABLE" statement with datatypes matching the source (or transformation) (user can manually modify) on a remote database, and then it loads the data, also displaying the growing rowcount as population occurs.
(This latter feature is slower than exporting to file followed by bcp, but is convenient with sub-million row datasets)

Can anyone advise if these features exist in the SqlDbx Professional product?  Is it limited to only allow, or require execution to screen prior to saving results?
I'm not locating this feature in the SqlDbx personal edition, but I would expect those to be "professional" features.

Please advise, thanks!

Offline

#2 2016-10-12 21:20:35

sqldbxhelp
Administrator

Re: Run query to file? Run query to external sandbox?

It is possible to export results to file without displaying them in SqlDbx Professional.
The way it can be done is the following:

cmd export = file C:\filename.csv
select * from table_name

Executing this batch will save result to filename.csv.
Unfortunately right now it will not do exactly what you looking for.
It will execute query without showing the results, retrieve all data and then save them to file.
Currently we support the mode you looking for only when exporting data from tables
We do not have any way to "execute to external database".

Offline

#3 2016-10-13 09:13:23

geolemon
Member

Re: Run query to file? Run query to external sandbox?

Thanks.
There's no option within the UI to run to file?

Typical development work occurs using "select top 1000 field1, field2, transformation1, etc" type of an approach to develop what are generally very complex queries, often more than a page of text (including CASE statements, comments and other markups). It might be unweidly (if possible) to have to copy/paste that entire statement into a command prompt, rather than within the tool used for query development.

I may be still set in my ways with the current tool, but to illustrate-
I'm used to developing/saving many complex queries within a SQL file (none are T-SQL), highlighting the individual queries as needed, and selecting "Export to table" or "Export to file" - and an appropriate wizard pops up walking through the necessary options (file type, delimiters, etc, or remote database, table options, etc).
Can you compare to that usage?

I'll try to explore further as I can, we may have purchased a license for the Professional version already.
I've played briefly with the Personal edition on a coworker's laptop, and been impressed with the features to enable rapid ad-hoc query development, which would be an asset and enhancement to our processes I'm sure, otherwise.
Thanks.

Last edited by geolemon (2016-10-13 09:20:56)

Offline

#4 2016-10-13 09:27:27

sqldbxhelp
Administrator

Re: Run query to file? Run query to external sandbox?

There's no command prompt involved.
"cmd" is internal SqlDbx command which you put in your script and it affects next query output.
We do not have user interface for selecting query and output results to file, but it looks like a good feature to have.
If you want to get more information please email to suppot at sqldbx and we can arrange phone call to discuss

Offline

#5 2016-10-14 08:58:04

geolemon
Member

Re: Run query to file? Run query to external sandbox?

Thanks.
Are there any options to change the file delimiter, or is the only possibility comma delimited?
CSV can be tough as we have fields containing commas.  Or are the text columns qualified with quotes during export?

I can go either way on the direct database-to-database extract.  It is convenient, allows us to develop and execute within one script and within one step, however it is slower so only useful for >1,000,000 row or so queries.  We may be fine with always extracting to file, and then using BCP or SQL Server Import Wizard for a subsequent import.
Thanks.

Last edited by geolemon (2016-10-14 09:00:09)

Offline

#6 2016-10-14 10:10:44

sqldbxhelp
Administrator

Re: Run query to file? Run query to external sandbox?

Yes you can change delimiters and quotes and some other settings for CSV.
Take a look at menu Tools->Options->Import/Export page in SqlDbx

Offline

Board footer

Powered by FluxBB