Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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:

INSERT INTO A_WITH_ADDITIONAL_COLUMN
SELECT * FROM A;

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?

share|improve this question
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 Oct 22 '12 at 20:12

2 Answers 2

up vote 3 down vote accepted

This is simple:

INSERT INTO A_WITH_ADDITIONAL_COLUMN
    SELECT null, A.* FROM A;
share|improve this answer

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.

share|improve this answer
    
please read the question once again... –  mnowotka Oct 22 '12 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. –  Stuart Moore Oct 22 '12 at 14:11
    
@stuart: You can also edit your answer. Or delete it. –  ypercube Oct 22 '12 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 Oct 22 '12 at 20:15

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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