CODEWITHSUNDEEP
sqloracle-databaseinsert-into
DanGordon
DanGordon

Reputation: 671

INSERT INTO using a query, and add a default value

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

Upvotes: 15

Views: 20210

Answers (3)

ibre5041
ibre5041

Reputation: 5288

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.

Upvotes: 3

Justin Cave
Justin Cave

Reputation: 231651

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

Upvotes: 4

Joe Stefanelli
Joe Stefanelli

Reputation: 135739

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;

Upvotes: 22

Related Questions