Loading from a column

[Demetris]

Hello there!
I have a table (table1) and one of its columns DATA_X is CHAR(660)
I will create another table (table2) similar to the above but i will
replace the DATA_X column with several columns (different data types)

The table1 is populated with data using a text file.
After the loading is finished on table1 i want to select all the data
from table1 and load it in table2. The DATA_X must be split in several
columns.

How do i perform this?

Thank you.

[Alistair Thomson]

Re: Loading from a column

Why not use sqlldr to load the data from the text file into table2? You can
use the fixed column position or a delimiter to separate the data into
different columns.

Alistair

"Demetris" <DMina@laiki.co mwrote in message
news:c5285638.0 402050340.3e482 dc0@posting.goo gle.com...

Hello there!
I have a table (table1) and one of its columns DATA_X is CHAR(660)
I will create another table (table2) similar to the above but i will
replace the DATA_X column with several columns (different data types)
>
The table1 is populated with data using a text file.
After the loading is finished on table1 i want to select all the data
from table1 and load it in table2. The DATA_X must be split in several
columns.
>
How do i perform this?
>
Thank you.

[Ed prochak]

Re: Loading from a column

DMina@laiki.com (Demetris) wrote in message news:<c5285638. 0402050340.3e48 2dc0@posting.go ogle.com>...

Hello there!
I have a table (table1) and one of its columns DATA_X is CHAR(660)
I will create another table (table2) similar to the above but i will
replace the DATA_X column with several columns (different data types)
>
The table1 is populated with data using a text file.
After the loading is finished on table1 i want to select all the data
from table1 and load it in table2. The DATA_X must be split in several
columns.
>
How do i perform this?
>
Thank you.

Use the various String functions to split that data according to your needs.
for example

table1
keyid number
data_x char(660)

table2
keyid number
datapart1 number
datapart2 varchar(100)

insert into table2 (keyid, datapart1, datapart2) values
( select keyid, to_num(substr(d ata_x,1,10)), ltrim(substr(da ta_x,50,100))
from table1 ) ;

My syntax may be off, but you get the idea.

More complex conversions will require PL/SQL or other programming language.

[Mark D Powell]

Re: Loading from a column

"Alistair Thomson" <alistair@despa mmed.comwrote in message news:<bvtavk$r3 2$1$8300dec7@ne ws.demon.co.uk> ...

Why not use sqlldr to load the data from the text file into table2? You can
use the fixed column position or a delimiter to separate the data into
different columns.
>
Alistair
>
"Demetris" <DMina@laiki.co mwrote in message
news:c5285638.0 402050340.3e482 dc0@posting.goo gle.com...

Hello there!
I have a table (table1) and one of its columns DATA_X is CHAR(660)
I will create another table (table2) similar to the above but i will
replace the DATA_X column with several columns (different data types)

The table1 is populated with data using a text file.
After the loading is finished on table1 i want to select all the data
from table1 and load it in table2. The DATA_X must be split in several
columns.

How do i perform this?

Thank you.

Detemtris, if the data is not in fixed absolute positions but is
relative to various delimiter characters or constants then look in the
SQL manual chapter on single row functions for substr, instr, and
other character manipulation functions.

HTH -- Mark D Powell --

[Demetris]

Re: Loading from a column

I cannot load the data from the text file into the second table for a
reason.
In the first table there are some columns that are populated with data
derived from functions and must be unique. Those unique data must be
present in the second table as well.

"Alistair Thomson" <alistair@despa mmed.comwrote in message news:<bvtavk$r3 2$1$8300dec7@ne ws.demon.co.uk> ...

Why not use sqlldr to load the data from the text file into table2? You can
use the fixed column position or a delimiter to separate the data into
different columns.
>
Alistair
>
"Demetris" <DMina@laiki.co mwrote in message
news:c5285638.0 402050340.3e482 dc0@posting.goo gle.com...

Hello there!
I have a table (table1) and one of its columns DATA_X is CHAR(660)
I will create another table (table2) similar to the above but i will
replace the DATA_X column with several columns (different data types)

The table1 is populated with data using a text file.
After the loading is finished on table1 i want to select all the data
from table1 and load it in table2. The DATA_X must be split in several
columns.

How do i perform this?

Thank you.

[Demetris]

Re: Loading from a column

Thank all of you for the information you have provided me.
It was very helpful!

Thanks again
D. M i n a

ed.prochak@magi cinterface.com (Ed prochak) wrote in message news:<4b5394b2. 0402051033.9bd3 846@posting.goo gle.com>...

DMina@laiki.com (Demetris) wrote in message news:<c5285638. 0402050340.3e48 2dc0@posting.go ogle.com>...

Hello there!
I have a table (table1) and one of its columns DATA_X is CHAR(660)
I will create another table (table2) similar to the above but i will
replace the DATA_X column with several columns (different data types)

The table1 is populated with data using a text file.
After the loading is finished on table1 i want to select all the data
from table1 and load it in table2. The DATA_X must be split in several
columns.

How do i perform this?

Thank you.

>
Use the various String functions to split that data according to your needs.
for example
>
table1
keyid number
data_x char(660)
>
table2
keyid number
datapart1 number
datapart2 varchar(100)
>
insert into table2 (keyid, datapart1, datapart2) values
( select keyid, to_num(substr(d ata_x,1,10)), ltrim(substr(da ta_x,50,100))
from table1 ) ;
>
My syntax may be off, but you get the idea.
>
More complex conversions will require PL/SQL or other programming language.