Output multiple queries to same table/file?

Question

I have a bunch of very simple SELECT statements. I would like to output them all to the same resultset (UI table or file). Here is the most recent thing I tried:

@export on;
@export set filename="c:\test.csv";
@export set CsvColumnDelimiter=",";
SELECT TOP 1 * FROM TableName WHERE ID = 1;
SELECT TOP 1 * FROM TableName WHERE ID = 2;
SELECT TOP 1 * FROM TableName WHERE ID = 3;
SELECT TOP 1 * FROM TableName WHERE ID = 4;
SELECT TOP 1 * FROM TableName WHERE ID = 5;
@export off;

Obviously the CSV file only contains ID 5 because it's just overwriting. Is there any way to append? Or, is there any SQL option outside of DBVis that will allow me to execute all these SQL queries into one result set?

are you able to UNION your SELECT statements so there is one result set? — devlin carnate, Dec 07 '15 at 17:07
Looks like that's what I needed. Want to post an answer? Or find a duplicate (now that I know it's a simple command) — THE JOATMON, Dec 07 '15 at 17:09
Not related to your question, but `select top n` queries without an order by clause might not yield the desired results. — Dan Bracuk, Dec 07 '15 at 17:56
Yeah this was a truncated example. Thanks for the note though. — THE JOATMON, Dec 07 '15 at 18:42
For the **@export set** command, add parameter **AppendFile="true"** and you will get all result sets in the file. Read more in: http://confluence.dbvis.com/display/UG92/Exporting+Query+Results — roger, Dec 08 '15 at 08:30
Awesome @roger, but would it include the column headers repeatedly as well? — THE JOATMON, Dec 08 '15 at 16:58

score 2 · Answer 1 · edited May 23 '17 at 12:34

2

One way to achieve this is to return one result set, using UNION ALL.

SELECT TOP 1 * FROM TableName WHERE ID = 1
UNION ALL
SELECT TOP 1 * FROM TableName WHERE ID = 2
UNION ALL
SELECT TOP 1 * FROM TableName WHERE ID = 3
UNION ALL
SELECT TOP 1 * FROM TableName WHERE ID = 4
UNION ALL
SELECT TOP 1 * FROM TableName WHERE ID = 5;

edited May 23 '17 at 12:34

Community

1
1

answered Dec 07 '15 at 17:13

devlin carnate

7,947
7
46
77

So I did `UNION` and it seems to work. What would be the difference? – THE JOATMON Dec 07 '15 at 17:20
To clarify, with `UNION` I get 5 results, as expected. What would/should I see with UNION ALL? Would there be any difference in this example? – THE JOATMON Dec 07 '15 at 17:21
1

An ordinary UNION eliminates duplicate rows (all values identical) from the result. A UNION ALL preserves duplicate rows in the result. http://docs.intersystems.com/cache20152/csp/docbook/DocBook.UI.Page.cls?KEY=RSQL_union#RSQL_B65538 – DAiMor Dec 07 '15 at 17:33
1

I linked `UNION ALL` in my answer with the explanation of the difference. I'm assuming since you're selecting * by ID that the results would never have duplicates. Thus, the UNION ALL. – devlin carnate Dec 07 '15 at 17:55

score 2 · Accepted Answer · answered Dec 09 '15 at 08:17

2

Scott, try:

@export on;
@export set filename="<outputfile.csv>" appendfile="true";
select * from tab;
@export set CsvIncludeColumnHeader="false";
select * from tab;
select * from tab;

This will export the first result set with column headers and the following result sets without column headers.

answered Dec 09 '15 at 08:17

roger

611
4
7

Thanks @roger! Are you the dev? – THE JOATMON Dec 09 '15 at 13:56
2

Yes I am in the DbVisualizer team – roger Dec 09 '15 at 18:03
Awesome, keep up the great work! I love the product. – THE JOATMON Dec 09 '15 at 18:03

score 0 · Answer 3 · edited Jun 05 '17 at 11:59

0

In IQ database the client can set an option

set option isql_show_multiple_result_sets = 'on';

2 Questions:

which database is this?
where in the multiple results sets can be returned?

edited Jun 05 '17 at 11:59

answered Jun 05 '17 at 08:15

Imran Khan

1
1

Output multiple queries to same table/file?

3 Answers3