Oracle Copy Data to Another Table

Oracle copy data to another table

You need an INSERT ... SELECT

INSERT INTO exception_codes( code, message )
  SELECT code, message
    FROM exception_code_tmp

Easy way to copy all values from one table to another with an extra column

Give the table you are selecting from an alias (t, for example), and you can then select all columns from this table using t.*, along with any extra columns you want to add.

Here's an example. I ran the below on my Oracle 18c XE database:

SQL> CREATE TABLE table2 (a INTEGER, b INTEGER, c INTEGER);

Table created.

SQL> CREATE TABLE table1 (a INTEGER, b INTEGER, c INTEGER, d INTEGER);

Table created.

SQL> INSERT INTO table2 (a, b, c) VALUES (1, 2, 3);

1 row created.

SQL> INSERT INTO table1 (a, b, c, d)
  2  SELECT t.*, 4
  3    FROM table2 t;

1 row created.

SQL> SELECT * FROM table1;

         A          B          C          D
---------- ---------- ---------- ----------
         1          2          3          4

Oracle - Copy column to another table with condition

You want an update here, not an insert:

UPDATE TABLE_B b
SET NEW_COLUMN = (SELECT COLUMN_TO_COPY FROM TABLE_A a WHERE a.TABLE_A_ID = b.TABLE_A_FK);

If you only want to make an update where an actual match happens between the two tables, then add an exists clause:

UPDATE TABLE_B b
SET NEW_COLUMN = (SELECT COLUMN_TO_COPY FROM TABLE_A a WHERE a.TABLE_A_ID = b.TABLE_A_FK)
WHERE EXISTS (SELECT 1 FROM TABLE_A a WHERE a.TABLE_A_ID = b.TABLE_A_FK);

Creating Duplicate Table From Existing Table

Use this query to create the new table with the values from existing table

CREATE TABLE New_Table_name AS SELECT * FROM Existing_table_Name; 

Now you can get all the values from existing table into newly created table.

How can I create a copy of an Oracle table without copying the data?

Just use a where clause that won't select any rows:

create table xyz_new as select * from xyz where 1=0;

Limitations

The following things will not be copied to the new table:

• sequences
• triggers
• indexes
• some constraints may not be copied
• materialized view logs

This also does not handle partitions

---

How to copy a table from one database to another in Oracle

The COPY command will be obsoleted in future releases of SQL*Plus. SQL*Plus User's Guide and Reference-Release 9.2

There are several methods like using datapump or CTAS. You can also use Database Link.

SQL> CREATE DATABASE LINK dblink_QA_1
CONNECT TO QA_1_app_prop_SCHEMA
IDENTIFIED BY password
USING '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = uat-blah.foo.foo.com)(PORT = 2501)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = uat)))'; 

And change your insert statement to-

SQL> INSERT INTO QA_2.app_prop (prop_id, prop_name, prop_value)
SELECT prop_id, prop_name, prop_value FROM QA_1.app_prop@dblink_QA_1 WHERE prop_id IN 
(SELECT prop_id FROM app_env@dblink_QA_1 WHERE function = 'CCC');

Copy data from one table to another- Oracle

declare
newId number;
begin
select nvl(max(person.pk),0) + 1 into newId from person;
for x in (
    select w.Name, p.Address
    from wife w inner join Person p
    on w.Person_id = P.pk) loop
   insert into Person(pk, Name,Address,Is_Married) values (newId ,x.Name ,x.Address,'Y');
   newId := newId  +1;
end loop;
commit;
end

---

Related Topics