Search code examples
oraclesqlfiddle

INSERT..SELECT in Oracle always fails with "SQL command not properly ended"


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.


Solution

  • 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.