4

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

This would 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 nullable, so I can't just run an update afterwards.

Thanks for any help!

Nick Brunt
  • 8,858
  • 8
  • 50
  • 81

5 Answers5

11

Specify the column and use a constant for the value (note you can mix constants and column references in a select clause). In this case we're specifying every row will get the constant 1 for column COL4.

insert into TABLEA (COL1, COL2, COL3, COL4)
select COL1, COL2, COL3, 1
from TABLEB;
lc.
  • 109,978
  • 20
  • 153
  • 183
  • Nice answer but no explanation of the syntax? What does the "select" part do o_O... someone who sees this for the first time is gonna have some questions... – solujic Jul 11 '17 at 08:08
2
insert into TABLEA (COL1, COL2, COL3, extra) 
select COL1, COL2, COL3, 1 
from TABLEB;
John Woo
  • 249,283
  • 65
  • 481
  • 481
1

Have you tried this?

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

Works on my computer :-)

dotvav
  • 2,726
  • 13
  • 30
1
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
Sabyasachi Mishra
  • 1,617
  • 1
  • 28
  • 48
Prasanth
  • 25
  • 4
0

You can select a constant from TableB

INSERT INTO tableA( col1, col2, col3, col4 )
  SELECT col1, col2, col3, 1 col4
    FROM tableA
Justin Cave
  • 221,607
  • 22
  • 353
  • 373