Copy table data

dennis.robinson · April 3, 2017, 5:26am

How do you copy records to another similar table in the same schema?

JohnDorlon · April 3, 2017, 5:47am

Find the table in the schema browser. Select one or more tables to copy and right-click. Choose “Copy data to another schema”. A wizard will guide you through the rest.

honglei · July 10, 2017, 7:55am

in my case, I don’t see the option “Copy data to another schema” when I select one table to copy and right click.

are there any other ways to copy table data?

JohnDorlon · July 10, 2017, 8:02am

Everyone should have this. It is not limited by any kind of license (except maybe freeware).

Did you go to the Schema Browser, find the table in the list on the left hand side, and right-click there?

If you did that, and don’t see it, what version of Toad do you have?

Another way to copy table data (assuming the columns are all the same)

If the tables are in the same database, you could run a query like this in the Editor: insert into .

select * from .

.

If they are in a different database, you could use the same statement with a database link: insert into .

@ select * from .

.

honglei · July 10, 2017, 8:23am

Hi John,

Thanks for your quick response.

I am using 12.10 freeware. I am new to Toad and Oracle.

What I really want to achieve is to make a backup copy of “table data” just in case.

Can I use another option “Export data” under Schema Brower to save the “table data” as excel file ?

If that option is ok, how do I import it back to replace the existing table data if I need to use this backup?

Thanks,

Hong Lei

From: John Dorlon [mailto:bounce-jdorlon@toadworld.com]

Sent: Monday, July 10, 2017 10:04 AM

To: toadoracle@toadworld.com

Subject: RE: [Toad for Oracle - Discussion Forum] Copy table data

RE: Copy table data

Reply by John Dorlon

Everyone should have this. It is not limited by any kind of license (except maybe freeware).

Did you go to the Schema Browser, find the table in the list on the left hand side, and right-click there?

If you did that, and don’t see it, what version of Toad do you have?

Another way to copy table data (assuming the columns are all the same)

If the tables are in the same database, you could run a query like this in the Editor: insert into .

select * from .

.

If they are in a different database, you could use the same statement with a database link: insert into .

@ select * from .

.

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle Forum notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

JohnDorlon · July 10, 2017, 8:28am

You can export and then re-import, but the easiest way to make a backup table is to do it in the database:

create table as select * from

Then if you need to restore it:

truncate table

;

insert into

select * from

and when you are done with the backup table…

drop table ;

honglei · July 10, 2017, 8:46am

Hi John,

I followed your instruction, After click “Table”, it opens another window. Assume the table name is the . Where can I choose the ?

Thanks,

Hong

From: John Dorlon [mailto:bounce-jdorlon@toadworld.com]

Sent: Monday, July 10, 2017 10:29 AM

To: toadoracle@toadworld.com

Subject: RE: [Toad for Oracle - Discussion Forum] Copy table data

RE: Copy table data

Reply by John Dorlon

You can export and then re-import, but the easiest way to make a backup table is to do it in the database:

create table as select * from

Then if you need to restore it:

truncate table

;

insert into

select * from

and when you are done with the backup table…

drop table ;

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle Forum notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

JohnDorlon · July 10, 2017, 8:48am

Those are all SQL commands that you can type into and run in the Editor.

gene.l.bradley · July 10, 2017, 8:52am

If you have the right privileges in the DB
John's suggestion is really the simplest, however

if you just want to copy the table to excel

select * from <table_name>

in the data grid, right click,

export data

excel instance

and you're done.

You'll need a current version of excel to export more than 65,000 rows

if you need to import back

from the menu

give it a name (a different name)

The wizzard will prompt you from there.)You will need to watch for datatypes and invalid column names etc)

then

follow the prompts

But I repeat, this is a lot more work, Johns recommendation of copying the table in the database is waaaay better!!!!

On Mon, Jul 10, 2017 at 10:25 AM, honglei bounce-honglei@toadworld.com wrote:

RE: Copy table data

Reply by honglei
Hi John,

Thanks for your quick response.

I am using 12.10 freeware. I am new to Toad and Oracle.

What I really want to achieve is to make a backup copy of “table data” just in case.

Can I use another option “Export data” under Schema Brower to save the “table data” as excel file ?

If that option is ok, how do I import it back to replace the existing table data if I need to use this backup?

Thanks,

Hong Lei

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or Unsubscribe from Toad for Oracle Forum notifications altogether.

Toad for Oracle - Discussion Forum

Flag this post as spam/abuse.

--
Gene L. Bradley Jr.

Systems Analyst

Office of Information Technology

Jackson State University

1400 J R Lynch Street

P.O. Box 17750

Jackson, MS 39217

ph 601.979.1042

fax 601.371.9146

email gbradley@jsums.edu

*In God we trust; all others bring data. * ~W.E. Deming

CONFIDENTIALITY STATEMENT

This electronic transmission is intended for the use of the individual or entity to which it is addressed and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If the reader of this message is not the intended recipient, or the employee or agent responsible for delivering the message to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by telephone (601) 979-1042. Thank you.

gene.l.bradley · July 10, 2017, 8:54am

create table <new_table_name> as
select * from <table_name_you_want_to_copy>;

Done

On Mon, Jul 10, 2017 at 10:47 AM, honglei bounce-honglei@toadworld.com wrote:

RE: Copy table data

Reply by honglei
Hi John,

I followed your instruction, After click “Table”, it opens another window. Assume the table name is the . Where can I choose the ?

Thanks,

Hong

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or Unsubscribe from Toad for Oracle Forum notifications altogether.

Toad for Oracle - Discussion Forum

Flag this post as spam/abuse.

--
Gene L. Bradley Jr.

Systems Analyst

Office of Information Technology

Jackson State University

1400 J R Lynch Street

P.O. Box 17750

Jackson, MS 39217

ph 601.979.1042

fax 601.371.9146

email gbradley@jsums.edu

*In God we trust; all others bring data. * ~W.E. Deming

CONFIDENTIALITY STATEMENT

This electronic transmission is intended for the use of the individual or entity to which it is addressed and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If the reader of this message is not the intended recipient, or the employee or agent responsible for delivering the message to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by telephone (601) 979-1042. Thank you.

honglei · July 10, 2017, 8:58am

Hi John,

Since I never done SQL before, after I typed what you showed me in the previous email. Where do I click to run?

Thanks,

Hong

From: John Dorlon [mailto:bounce-jdorlon@toadworld.com]

Sent: Monday, July 10, 2017 10:50 AM

To: toadoracle@toadworld.com

Subject: RE: [Toad for Oracle - Discussion Forum] Copy table data

RE: Copy table data

Reply by John Dorlon

Those are all SQL commands that you can type into and run in the Editor.

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle Forum notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

honglei · July 10, 2017, 9:04am

I found a button, called “Execute via Toad Script Runner”.

Is this the correct way to run the SQL that I typed?

Thanks,

Hong

From: Lei, Hong (Nokia - US/Naperville)

Sent: Monday, July 10, 2017 10:57 AM

To: toadoracle@toadworld.com

Cc: Lei, Hong (Nokia - US/Naperville) hong.lei@nokia.com

Subject: RE: [Toad for Oracle - Discussion Forum] Copy table data

Hi John,

Since I never done SQL before, after I typed what you showed me in the previous email. Where do I click to run?

Thanks,

Hong

From: John Dorlon [mailto:bounce-jdorlon@toadworld.com]

Sent: Monday, July 10, 2017 10:50 AM

To: toadoracle@toadworld.com

Subject: RE: [Toad for Oracle - Discussion Forum] Copy table data

RE: Copy table data

Reply by John Dorlon

Those are all SQL commands that you can type into and run in the Editor.

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle Forum notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

JohnDorlon · July 10, 2017, 9:07am

that’s one way. the easiest is to hit F9. F9 is a shortcut for the “Execute/Compile Statement at Caret” button.

gene.l.bradley · July 10, 2017, 9:18am

On Mon, Jul 10, 2017 at 10:59 AM, honglei bounce-honglei@toadworld.com wrote:

RE: Copy table data

Reply by honglei
Hi John,

Since I never done SQL before, after I typed what you showed me in the previous email. Where do I click to run?

Thanks,

Hong

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or Unsubscribe from Toad for Oracle Forum notifications altogether.

Toad for Oracle - Discussion Forum

Flag this post as spam/abuse.

--
Gene L. Bradley Jr.

Systems Analyst

Office of Information Technology

Jackson State University

1400 J R Lynch Street

P.O. Box 17750

Jackson, MS 39217

ph 601.979.1042

fax 601.371.9146

email gbradley@jsums.edu

*In God we trust; all others bring data. * ~W.E. Deming

CONFIDENTIALITY STATEMENT

This electronic transmission is intended for the use of the individual or entity to which it is addressed and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If the reader of this message is not the intended recipient, or the employee or agent responsible for delivering the message to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by telephone (601) 979-1042. Thank you.

honglei · July 10, 2017, 9:41am

It works great. Thank you all for inputs.

One more question, how do I delete an entire table data (ex. the extra backup table data I created) ?

From: gene.l.bradley [mailto:bounce-genelbradley@toadworld.com]

Sent: Monday, July 10, 2017 11:20 AM

To: toadoracle@toadworld.com

Subject: RE: [Toad for Oracle - Discussion Forum] Copy table data

RE: Copy table data

Reply by gene.l.bradley

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle Forum notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

JohnDorlon · July 10, 2017, 9:44am

You’re welcome. Go back and read my message with the SQL commands to see how to drop a table when you are done with it.

JohnDorlon · July 10, 2017, 9:46am

Well, let me be more clear…

To delete the data from a table, but keep the table:

truncate table

;

to remove the table from the database:

drop table

;

gene.l.bradley · July 10, 2017, 9:46am

truncate table <table_name_you_created>;

then

drop table <table_name_you_created> purge;

On Mon, Jul 10, 2017 at 11:42 AM, honglei bounce-honglei@toadworld.com wrote:

RE: Copy table data

Reply by honglei
It works great. Thank you all for inputs.

One more question, how do I delete an entire table data (ex. the extra backup table data I created) ?

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or Unsubscribe from Toad for Oracle Forum notifications altogether.

Toad for Oracle - Discussion Forum

Flag this post as spam/abuse.

--
Gene L. Bradley Jr.

Systems Analyst

Office of Information Technology

Jackson State University

1400 J R Lynch Street

P.O. Box 17750

Jackson, MS 39217

ph 601.979.1042

fax 601.371.9146

email gbradley@jsums.edu

*In God we trust; all others bring data. * ~W.E. Deming

CONFIDENTIALITY STATEMENT

This electronic transmission is intended for the use of the individual or entity to which it is addressed and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If the reader of this message is not the intended recipient, or the employee or agent responsible for delivering the message to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by telephone (601) 979-1042. Thank you.