I have two tables that have the same structure except one has an additional column that should be auto-incremented. Sequence and trigger are defined.

I want to copy all data from one table to another using this SQL statement:


But I'm getting error:

SQL Error: ORA-00947: not enough values

Which is fine because there is one more column in the target table.

How do I append an additional NULL at the beginning of every row of the source table in order to be able to fill the target WITHOUT enumerating all columns of the source table in the query?

  • 1
    Of course it is is a VERY POOR practice to ever do an insert without specifying the columns names. VERY BAD things can happen if people change the table structure. Please do not use SQL antipatterns in your coding.
    – HLGEM
    Commented Oct 22, 2012 at 20:12

2 Answers 2


This is simple:

    SELECT null, A.* FROM A;

INSERT INTO A_WITH_ADDITIONAL_COLUMN (newcol, col1, col2, col3,...)
    SELECT NULL, A.col1, A.col2,a.col3,..... FROM A;

Should do what you're asking.

  • please read the question once again...
    – mnowotka
    Commented Oct 22, 2012 at 14:03
  • 2
    little harsh as the edit to the question to add that constraint coincided with me typing my answer, but that's life. Commented Oct 22, 2012 at 14:11
  • @stuart: You can also edit your answer. Or delete it. Commented Oct 22, 2012 at 14:54
  • 1
    As it happens this is the correct answer whether it was what the OP wanted to hear anyway. It is a SQL antipattern to use select * or fail to enumerate the columns in an insert statement.
    – HLGEM
    Commented Oct 22, 2012 at 20:15

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.