Q
Problem solve Get help with specific problems with your technologies, process and projects.

Retrieving all data from one table and putting it into another table

I have two tables: "test" and "Test1," each with the same structure, like:

 CREATE TABLE Test (name   varchar2(30),

 info   CLOB)

 CREATE TABLE Test1 (name   varchar2(30), info   CLOB)

I want to retrieve all the data from Test and put it in Test1. How can I achieve this through a simple query? I am facing this problem with the CLOB data type.

With LOBs, simple SQL statements can be used to move data from one table to another. To show you how easy this is, I will step through a simple example. First, I'll create the two tables as you have defined:

SQL> create table test (name varchar2(30), info clob);

Table created.

SQL> create table test1 (name varchar2(30), info clob);

Table created.

Next, I'll add some data to this table:

SQL> insert into test values ('Bob','This is clob data');

1 row created.

SQL> insert into test values ('Jack','This is more clob data');

1 row created.

SQL> commit;

Commit complete.

To simply copy all rows of data from one table to the other, a simple INSERT SELECT statement is used:

SQL> insert into test1 select * from test;

2 rows created.

SQL> commit;

Commit complete.

SQL> select * from test1;

NAME
------------------------------
INFO
-----------------------------------------------------------------------
Bob
This is clob data

Jack
This is more clob data

As you can see, a simple SQL statement can be used to move data from one table to another, even those columns defined as CLOBs. The LONG and LONG RAW datatypes are not able to be moved with simple SQL statements in this way.

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close