How do I copy over data from one table to another which has new columns?-CodePudding

I have Table A and Table B.

Both used to have same content but now table A has a few extra non nullable columns. I am trying to copy over data from Table B into Table A but getting an error when I try this query,

INSERT INTO TABLE_A (SELECT * FROM TABLE_B);

This throws the error, ORA-00947: not enough values

INSERT INTO TABLE_A(Col1, Col2, Col3, Col4)
VALUES ((SELECT Col1 FROM TABLE_B), (SELECT Col3 FROM TABLE_B), 'New Column', 'New Column');

This throws the error, ORA-01427: single-row subquery returns more than one row

CodePudding user response：

Try this:

INSERT INTO TABLE_A (Col1, Col2, Col3, Col4)
(SELECT Col1, Col3, 'New Column', 'New Column' FROM TABLE_B);

This should return all the rows from TABLE_B with the correct number of columns.

CodePudding user response：

As you mentioned in question itself table structure of A and B are different. This is the reason why INSERT INTO SELECT.. is failing.

Below is the query for reference:

INSERT INTO TABLE_A (Col1, Col2, Col3, Col4)
(
SELECT Col1, Col3, 'DUMMY', 'DUMMY' 
FROM TABLE_B
);