0

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) –  Dec 07 '15 at 17:09
  • 1
    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. –  Dec 07 '15 at 18:42
  • 1
    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? –  Dec 08 '15 at 16:58

2 Answers2

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;
Community
  • 1
  • 1
devlin carnate
  • 8,309
  • 7
  • 48
  • 82
  • So I did `UNION` and it seems to work. What would be the difference? –  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? –  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
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.

roger
  • 641
  • 4
  • 7