Insert in append mode

Hi folks,

i have 2 tables named F571226, F751226_10march in two different schema testdta, backupdta respectively
Note: F571226 doesnt have any data in testdta schema, both have same columns names
Now my requirement is to insert data into F571226 from F571226_10march

but when i try to insert by below command it taking too time and failed

insert /*+ APPEND PARALLEL*/ into testdta.F571226 value select * from backupdta.F571226_10march14

shall i specify each field (columns name) instead of specifying source table (backupdta.F571226_10march) in the above syntax?. source table has got around 25000000 rows


please correct my syntax if it is wrong

insert /*+ APPEND PARALLEL */ nologging into testdta.F571226 value select col1, col2,col3,col4,col5 ................... from backupdta.F571226_10march.


thanks & regards

Unless the table is partitioned, I don't see how the parallel hint will help. Since the destination table is empty,
you might want to get the DDL for the indexes and constraints, drop them then do the insert without parallel.
You can then create the indexes and constraints.

If the indexes and constraints are in place during the insert ever row needs to be checked to see if it is violating
a constraint and each index needs to be updated for each row.

Quote:

---

Originally Posted by younusdba

... and failed...

---

Could you please share the returned error stack, please?

Quote:

---

Originally Posted by younusdba

shall i specify each field ...

---

No, both tables have the same structure therefore no need to specify column by column.

Quote:

---

Originally Posted by younusdba

Hi folks,

i have 2 tables named F571226, F751226_10march in two different schema testdta, backupdta respectively
Note: F571226 doesnt have any data in testdta schema, both have same columns names
Now my requirement is to insert data into F571226 from F571226_10march

but when i try to insert by below command it taking too time and failed

insert /*+ APPEND PARALLEL*/ into testdta.F571226 value select * from backupdta.F571226_10march14

shall i specify each field (columns name) instead of specifying source table (backupdta.F571226_10march) in the above syntax?. source table has got around 25000000 rows


please correct my syntax if it is wrong

insert /*+ APPEND PARALLEL */ nologging into testdta.F571226 value select col1, col2,col3,col4,col5 ................... from backupdta.F571226_10march.


thanks & regards

---

You can try using Datapump (impd) with TABLE_EXISTS_ACTION and REMAP_SCHEMA import parameters -
http://www.oraclesql-plsql.com/Datapump.html

Hope this would help

You can try using Datapump (impdp) with TABLE_EXISTS_ACTION and REMAP_SCHEMA import options -
http://www.oraclesql-plsql.com/Datapump.html

Hope this solves your issu

Quote:

---

Originally Posted by younusdba

Hi folks,
i have 2 tables . . . F571226 doesnt have any data
. . . E t c . . .

---

Try CTAS:

Code:

---

DROP TABLE Testdta.F571226 CASCADE CONSTRAINTS;

CREATE TABLE Testdta.F571226
NOLOGGING
AS
  SELECT *
    FROM Backupdta.F571226_10march;

---

:p