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
  • 9,533
  • 10
  • 54
  • 83

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.
  • 113,939
  • 20
  • 158
  • 187
  • 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
  • 258,903
  • 69
  • 498
  • 492
2
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,677
  • 2
  • 31
  • 49
Prasanth
  • 35
  • 4
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,808
  • 15
  • 31
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
  • 227,342
  • 24
  • 367
  • 384