1

I am proficient in SQL-Server and other forms of SQL, but am trying to learn Oracle SQL. For some reason I cannot get even the simplest form of INSERT INTO .. SELECT .. to work, it always fails with "SQL command not properly ended."

Here is my current example:

CREATE TABLE table1 (year INT, id INT, dat DATE, categ VARCHAR(99));

INSERT INTO table1
(year, id, dat, categ)
select year, id, dat, categ from table1 where id=5000 and year=2013;

Here's a SqlFiddle of it: http://sqlfiddle.com/#!4/c4d34/1

I cannot seem to figure out what's wrong here. I have checked about a dozen other related question here at SO and more than another dozen on Google but all of the answers either don't apply, or don't work. I have also tried about a million variations of the commands above, nothing seems to work.

Any help greatly appreciated.

FWIW, I now think that this is just a SQLFiddle problem, as many had contended.

The Oracle User who reported the problem to me with my code, was of course using the full SQL statement, before I had stripped it down to try to isolate the problem. That query had a completely different problem that just happened to report the same error in SQLFiddle. Specifically, its problem was that I was using As for table aliases, which apparently are invalid in Oracle (or perhaps, just in the query I had written).

In any event, sincere thanks to all who tried to help me.

Improve this question
10
  • @Linger That generates a "Missing Expression" error. Commented Mar 10, 2014 at 17:17
  • Which tool do you use to run that script? The web interface from Oracle-XE (Express) does not allow you to run more than one statement as a script. If you are using the XE web interface (in the browser) you are better of dumping that right away and use a proper GUI client (e.g. SQL Developer or any other, even SQL*Plus would be better) Commented Mar 10, 2014 at 17:33
  • Unfortunately, I don't have Oracle myself, I have to use SQLFiddle right now, so I am not sure how to answer that. The SQLFiddle tool does seem to allow both DDL and multiple DMLs for all other cases I have tried. (I welcome any cheap way to get Oracle on my desktop, a'la SQL Server's Developer Edition). Commented Mar 10, 2014 at 17:36
  • works just fine for me. In SQL Developer (free IDE from Oracle), put that into a SQL Worksheet and hit F5 (Run script). Commented Mar 10, 2014 at 17:37
  • 2
    This is not a general Oracle problem (your script works just fine in my SQL client). And it does work in SQLFiddle if you use the insert in the panel on the left hand side: sqlfiddle.com/#!4/c5678/1 Commented Mar 10, 2014 at 17:41

3 Answers 3

Reset to default
4 
CREATE TABLE table1 (year INT, id INT, dat DATE, categ VARCHAR(99))
/

INSERT INTO table1
(year, id, dat, categ)
select year, id, dat, categ from table1 where id=5000 and year=2013

This works, that is, if you paste both statements in the left (schema) window in SQL fiddle. I dont' think SQL Fiddle allows insert..select in the SQL window at all.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

SQLFiddle does allow INSERTS in the SQL window. INSERT..VALUES(..) works just fine. And INSERT..SELECT.. works for other DBMSs in SQLFiddle.
And ";" is the chosen separator in both of my SQLFiddle windows of my example.
Be that as it may: the statement works in the left window, while in the right window it fails, even without the semicolon.
@RBarryYoung - as others have pointed out, INSERT...SELECT... in the right panel in an Oracle SQLFiddle fails. Move it to the left panel (see this SQLFiddle) and it works fine. Share and enjoy.
2

SQL Fiddle

CREATE TABLE table1 (year INT, id INT, dat DATE, categ VARCHAR(99))
//

INSERT INTO table1 (year, id, dat, categ)
SELECT year, id, dat, categ 
FROM table1 
WHERE id = 5000 AND year=2013
//
Improve this answer

4 Comments

This just generate more errors: "Missing or invalid option" after the first and "Missing Expression" after the second.
Your SQLfiddle, has everything in the Build-Schema window, so it never executes the INSERT..SELECT in the Run SQL window.
OK, the "Missing or invalid option" error on the CREATE TABLE was cleared by changing the "statement terminator" dropdown option on SQLFiddle. But the original error remains when I try to execute your INSERT in the RunSQL window.
On that is not the problem one can select and insert data into same table.
2

I don't know why you are facing this problem but there is no issue with syntax

I think it is just how you are executing the query on fiddle i just changed the execution flow and moved Insert statement in schema build section then the whole thing worked fine without changing a word (but i have inserted some sample data to show the exact working)

see this http://sqlfiddle.com/#!4/38e62/1

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.