Oracle INSERT INTO using SELECT

Question

I need to duplicate some columns rows in Oracle.

I have a table customer_address and column address_type where 1 is delivery address, 2 is delivery address etc.

I have this columns that I need to duplicate:

customer_no
customer_name
customer_email

So for example at the moment

 CUSTOMER_NO ADDRESS_TYPE CUSTOMER_NAME CUSTOMER_EMAIL
 ----------- ------------ ------------- --------------
 40          1            Some Customer [email protected]

but what I want is

 CUSTOMER_NO ADDRESS_TYPE CUSTOMER_NAME CUSTOMER_EMAIL
 ----------- ------------ ------------- --------------
 40          1            Some Customer [email protected]
 40          2            Some Customer [email protected]

Is it possible to do it?

Update

Thanks for the answer @a_horse_with_no_name, but when I query the table the results seem odd, I get the column names twice, is this correct or did I miss something that has caused this?

For example

select customer_no, address_type, customer_name, customer_email
from   customer_address
where  customer_no = 40;

shows

 CUSTOMER_NO ADDRESS_TYPE CUSTOMER_NAME CUSTOMER_EMAIL
 ----------- ------------ ------------- --------------
 40          1            Some Customer [email protected]
 CUSTOMER_NO ADDRESS_TYPE CUSTOMER_NAME CUSTOMER_EMAIL
 ----------- ------------ ------------- --------------
 40          2            Some Customer [email protected]

Thanks

user330315 · Accepted Answer · 2014-11-04 08:54:38Z

3

insert into customer_address (customer_no, address_type, customer_name, customer_email)
select customer_no,
       2,
       customer_name, 
       customer_email
from customer_address
where customer_id = 40
  and address_type = 1;
commit;

For a complete description of the INSERT syntax, please see the manual:
http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_9014.htm#SQLRF01604

answered Nov 4, 2014 at 8:54

user330315

Sign up to request clarification or add additional context in comments.

2 Comments

user330315 Over a year ago

That is the output generated by SQLPlus. Use a different tool or configure SQLPlus differently (but that is a different question, don't change this one)

user4171014 Over a year ago

I just done the same query at another site and I don't get the double headers. Is that just because the Oracle or SQL*Plus version is different?

Collectives™ on Stack Overflow

Oracle INSERT INTO using SELECT

Update

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Update

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related