CODEWITHSUNDEEP
sqloracle-database
user4171014
user4171014

Reputation: 11

Oracle INSERT INTO using SELECT

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

Upvotes: 0

Views: 18459

Answers (1)

user330315
user330315

Reputation: 

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

Upvotes: 3

Related Questions