Combining query blocks with UNION, INTERSECT or EXCEPT is rather common.

How can you put these combined results into a table?

It seems that both SELECT..INTO and INSERT..SELECT and do work with UNIONs and the like:

drop table if exists LT_TestResult;
select 1 as ID, 'Query block 1' as Description
   into local temporary table LT_TestResult
   from dummy
union all
select 2, 'Query block 2'
   from dummy
union all
select 3, 'Query block 3'
   from dummy
order by 1;

select * from LT_TestResult order by 1;

This returns 3 rows, i.e. the SELECT...INTO has apparently used the combined result set although the INTO clause is just specfied for the first query block (and is not allowed for the further query blocks).

The same does work with INSERT SELECT:

truncate table LT_TestResult;

insert into LT_TestResult
select 1 as ID, 'Query block 1' as Description
from dummy
union all
select 2, 'Query block 2'
from dummy
union all
select 3, 'Query block 3'
from dummy
order by 1;

select * from LT_TestResult order by 1;

This also returns 3 rows although according to the docs INSERT expects a (single) SELECT statement and not a combined query.


So, in a nutshell, this works as desired as of V17.0.10.6315 but I think it is not really documented that way.

If this is expected behaviour, I'd suggest to add this to the docs.

asked 15 Oct '21, 08:56

Volker%20Barth's gravatar image

Volker Barth
39.8k358546815
accept rate: 34%

edited 25 Oct '21, 03:29

(Of course one could use the combined query within a derived query and use the derived query as base for SELECT..INTO and INSERT..SELECT, as other DBMSes seems to require - but if that's not necessary, why should one?)

(15 Oct '21, 08:59) Volker Barth

The INSERT SELECT UNION syntax seems natural, but the SELECT INTO UNION syntax is a surprise... thank you for telling us!

IMO the problem with the docs is that UNION is described as a "statement" when it fact it is an operator like JOIN.

UNION may be a funky operator, but so are many other operators such as LATERAL.

...and then there's the "recursive union" which is something else altogether, neither statement nor operator, perhaps a "staterator" :)

(15 Oct '21, 10:15) Breck Carter
Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×231
×91
×11

question asked: 15 Oct '21, 08:56

question was seen: 465 times

last updated: 25 Oct '21, 03:29