How to append a column to an inner SQL statement

insertmigrationoracle

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?

Best Answer

This is simple:

INSERT INTO A_WITH_ADDITIONAL_COLUMN
    SELECT null, A.* FROM A;

Related Solutions

How to verify that next sequence value can be used as a new primary key

The values of the PK comes from different sources: horrible. Using the sequence would be the preferred way. You can not tell what exactly is the next value for the sequence by lookin in the dictionary. There is caching in effect, obviously for performance reasons. If you look in the dictionary you will find the value that is valid after the cache ran out of numbers. What you don't know, is how far the cache is used.

    drop sequence z;    
    create sequence z;  -- default cache size 20
    select last_number from user_sequences where sequence_name = 'Z'; --> shows 1
    select z.nextval from dual; --> shows 1
    select last_number from user_sequences where sequence_name = 'Z'; --> shows 21

Can you ignore the pre entered value for the PK, so effectively only using the sequence? The way it works now is almost as bad as querying the max(pk)+1 from the table.

Postgresql – INSERT trigger dying with with #target columns not equal to #expressions, but they are equal

Remove parens around column list in select. This doesn't do what you think it does.

Best Answer

Related Solutions

How to verify that next sequence value can be used as a new primary key

Postgresql – INSERT trigger dying with with #target columns not equal to #expressions, but they are equal

Related Question