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--;

score 22 · Accepted Answer · answered Apr 02 '15 at 19:40

22

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;

answered Apr 02 '15 at 19:40

Joe Stefanelli

132,803
19
237
235

I feel silly for not knowing I could do that. Worked just fine. – DanGordon Apr 02 '15 at 19:56

score 4 · Answer 2 · answered Apr 02 '15 at 19:40

4

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

answered Apr 02 '15 at 19:40

Justin Cave

227,342
24
367
384

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? – DanGordon Apr 02 '15 at 19:56
1

@DanGordon - 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. – Justin Cave Apr 02 '15 at 20:06

score 3 · Answer 3 · answered Apr 03 '15 at 10:37

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.

INSERT INTO using a query, and add a default value

3 Answers3