Is it possible to copy all data from one table to another table plus an additional column?

Question

I have two tables that are slightly different from each other. Table A has 4 columns, and in Table B only 3. I want to copy all the data from Table B to Table A, but I also want to populate the extra column with a value of 1 for each row.

This will work, if not for the extra column:

 insert into TABLEA (COL1, COL2, COL3) select COL1, COL2, COL3 from TABLEB; 

Unfortunately, the extra column in table A is not NULL, so I cannot start the update right away.

Thanks for any help!

Answer 1

Specify a column and use a constant for the value (note that you can mix constants and column references in the select clause). In this case, we indicate that each row will receive the constant 1 for column COL4 .

 insert into TABLEA (COL1, COL2, COL3, COL4) select COL1, COL2, COL3, 1 from TABLEB; 

Answer 2

 insert into TABLEA (COL1, COL2, COL3, extra) select COL1, COL2, COL3, 1 from TABLEB; 

Answer 3

Have you tried this?

 insert into TABLEA (COL1, COL2, COL3, COL4) select COL1, COL2, COL3, 'Whatever' as COL4 from TABLEB; 

Works on my computer :-)

Answer 4

 INSERT INTO tableA SELECT a.*,<VALUE FOR THE EXTRA COLUMN> FROM tableB a 

For example, if the extra column in tableA is sys_creation_date , then

 INSERT INTO tableA SELECT a.*,sysdate FROM tableB a 

OR

 INSERT INTO tableA SELECT a.*,'10-Jan-2013' FROM tableB a 

Answer 5

You can select a constant from TableB

 INSERT INTO tableA( col1, col2, col3, col4 ) SELECT col1, col2, col3, 1 col4 FROM tableA