INSERT INTO using a query, and add a default value

Question

I want run an INSERT INTO table SELECT... FROM... The problem is that the table that I am inserting to has 5 columns, whereas the table I am selecting from has only 4. The 5th column needs to be set do a default value that I specify. How can I accomplish this? The query would be something like this (note: this is Oracle):

INSERT INTO five_column_table
     SELECT * FROM four_column_table
     --and a 5th column with a default value--;

Sides

Answer 1

Just add the default value to your select list.

INSERT INTO five_column_table
    SELECT column_a, column_b, column_c, column_d, 'Default Value'
       FROM four_column_table;

Landreth

I feel silly for not knowing I could do that. Worked just fine. – Sides

Answer 2

Just select the default value in your SELECT list. It's always a good idea to explicitly list out columns so I do that here even though it's not strictly necessary.

INSERT INTO five_column_table( col1, col2, col3, col4, col5 )
  SELECT col1, col2, col3, col4, 'Some Default'
    FROM four_column_table

If you really don't want to list out the columns

INSERT INTO five_column_table
  SELECT fct.*, 'Some Default'
    FROM four_column_table fct

Allometry

What is the reason you feel all the columns should be explicitly named? So that someone else reading the query won't have to go look up that table or something? – Sides

@Sides - Yes. Also so your code doesn't fail if someone adds another column to the table with a default value. And so that your code doesn't depend on the order of columns in a table which may differ across environments. – Allometry

Answer 3

Oracle supports a keyword DEFAULT for this purpose:

insert all
into five_column_table( col1, col2, col3, col4, col5 )
VALUES( col1, col2, col3, col4, DEFAULT)
SELECT col1, col2, col3, col4
FROM four_column_table;

But in your case I had to use multi-table insert. DEFAULT keyword can be used only in values clause.

Overstride