MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

SQL Insert from Query

with 5 comments

Sometimes my students find new errors that I’ve never seen. One student did that this week by including an ORDER BY clause in a subquery that feeds an INSERT statement. It raises an ORA-00907 exception, like:

ORA-00907: missing right parenthesis

You can’t include a subquery with an ORDER BY clause because it generates an error. The reason is simple. A subquery can’t perform a sort operation inside a subquery. Here’s a quick demonstration:

DROP TABLE destination;
CREATE TABLE destination
( destination_id    NUMBER
, destination_name  VARCHAR2(20));
 
INSERT INTO destination
(
  SELECT   1,'Sample1' FROM dual
  UNION ALL
  SELECT   2,'Sample2' FROM dual
  ORDER BY 1 DESC
);

If you remove the ORDER BY clause, the statement works without a problem. For example, here’s the working version:

INSERT INTO destination
(
  SELECT   1,'Sample1' FROM dual
  UNION ALL
  SELECT   2,'Sample2' FROM dual
);

Alternatively, you can include an ORDER BY clause when you remove the parentheses from around the subquery. This is an example:

INSERT INTO destination
  SELECT   1,'Sample1' FROM dual
  UNION ALL
  SELECT   2,'Sample2' FROM dual
  ORDER BY 1 DESC;

I hope this helps anybody who runs into the problem.

Written by maclochlainn

June 14th, 2014 at 10:40 pm

5 Responses to 'SQL Insert from Query'

Subscribe to comments with RSS or TrackBack to 'SQL Insert from Query'.

  1. […] a subquery in an Oracle INSERT statement […]

  2. […] the limit of using a subquery in an Oracle INSERT statement […]

  3. […] the limit of using a subquery in an Oracle INSERT statement […]

  4. Hi
    Well, just remove the parenthesis, and it will work, with the order by.

    ghassan

    15 Jun 14 at 3:51 pm

  5. Salem, Yes, it does. It begs the question why it works with parentheses around the statement in all other events but not with a set operator. It’s funny but I’ve worked with Oracle SQL for 25+ years and never run into this … it just says there’s always more to learn. ;-)

    maclochlainn

    15 Jun 14 at 4:17 pm

Leave a Reply