OraFAQ Forum: Server Utilities » Is there an efficient way to copy lots of data from a table to another one?

Home » RDBMS Server » Server Utilities » Is there an efficient way to copy lots of data from a table to another one?

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Is there an efficient way to copy lots of data from a table to another one? [message #140927]

Thu, 06 October 2005 04:46

george.wei
Messages: 4
Registered: October 2005

Junior Member

Dear all,

I have to synchronize data in two tables, the rule is:

1.Clear data in table B first
2.All records in table A must be appended into table B
3.If a field in table A does not exist in table B, it is ignored

The current solution is:

1.Find which fields exist in both table
2.Generate an INSERT statement for each record, write these statements into a script file
3.Insert a COMMIT statement into the script for each 100 records. Use this statement to make the transaction small
4.Run the script

But this solution is inefficient. Is there a better way to perform this job? Thanks for any clues.

Regards,
George

Report message to a moderator

Re: Is there an efficient way to copy lots of data from a table to another one? [message #140955 is a reply to message #140927]

Thu, 06 October 2005 07:09

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

>>3.Insert a COMMIT statement into the script for each 100 records. Use this statement to make the transaction small

That will be the worst of all.
Insert using APPEND hind and nologging option.

1. truncate table B.
2. insert into table_B ( select c1,c2,anycolumn you want from table_A)
3. Commit.

or
1. drop tableB.
2. create table B as select * from a;
3. drop unwanted columns in B.

Report message to a moderator

Re: Is there an efficient way to copy lots of data from a table to another one? [message #141737 is a reply to message #140955]

Tue, 11 October 2005 20:25

george.wei
Messages: 4
Registered: October 2005

Junior Member

Mahesh Rajendran wrote on Thu, 06 October 2005 07:09

Insert using APPEND hind and nologging option.

Thanks Mahesh for your reply first.

I do know the APPEND hint, but I haven't heard the nologging option yet. Can somebody give me more detailed information or a sample? Thanks.

Regards,
George

Report message to a moderator

Re: Is there an efficient way to copy lots of data from a table to another one? [message #141739 is a reply to message #141737]

Tue, 11 October 2005 20:35

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

insert /*+ append */ into x nologging select * from y

For more information, search the forum

Report message to a moderator

Previous Topic:	Package to load files
Next Topic:	data transfer from excel to oracle

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Nov 09 01:03:12 CST 2024