473,320 Members | 1,828 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Loading from a column

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.
Jul 19 '05 #1
5 4666
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" <DM***@laiki.com> wrote in message
news:c5**************************@posting.google.c om...
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.

Jul 19 '05 #2
DM***@laiki.com (Demetris) wrote in message news:<c5**************************@posting.google. 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(data_x,1,10)), ltrim(substr(data_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.
Jul 19 '05 #3
"Alistair Thomson" <al******@despammed.com> wrote in message news:<bv*******************@news.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" <DM***@laiki.com> wrote in message
news:c5**************************@posting.google.c om...
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 --
Jul 19 '05 #4
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" <al******@despammed.com> wrote in message news:<bv*******************@news.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" <DM***@laiki.com> wrote in message
news:c5**************************@posting.google.c om...
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.

Jul 19 '05 #5
Thank all of you for the information you have provided me.
It was very helpful!

Thanks again
D. M i n a

ed********@magicinterface.com (Ed prochak) wrote in message news:<4b*************************@posting.google.c om>...
DM***@laiki.com (Demetris) wrote in message news:<c5**************************@posting.google. 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(data_x,1,10)), ltrim(substr(data_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.

Jul 19 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Bill Stock | last post by:
The few times in the past that I've loaded unbound data, I've tended to cheat and use temp tables (not really unbound) or use code for small datasets. I'm currently involved in a project that...
5
by: John Richardson | last post by:
I've been bothered for some time about my DataGrid not populating my rows very quickly. I have about 10K rows loading into the grid. I create a datatable dt with 2 columns, an ID and a display. ...
4
by: Fred Nelson | last post by:
I have an applicatioin that I'm writing that uses a "case" file that contains over 350 columns and more may be added in the future. I would like to create a dataset with all the column names and...
5
by: schapopa | last post by:
I have a flex grid and I am loading data to this flex grid in this way While sqldr.Read j = j + 1 MSFlexGrid1PLSummary.set_TextMatrix(MSFlexGrid1PLSummary.Row, MSFlexGrid1PLSummary.Col,...
7
by: tojigneshshah | last post by:
Hi, I am loading data from ascii delimiter file and some of the rows are getting rejected while loading. 1.0|11487.0|FQ|105061.0|332735.0|01|X.NNIE HATFIELD|1992-06-25 00:00:00|1992-...
1
by: aj | last post by:
DB2 WSUE LUW v8.2 FP4 (aka v8.1 FP11) RHEL AS 4 I am EXPORTing in IXF format from one schema and then LOADing into another schema. The DB modeling tool I am using likes to put the PK columns...
1
by: gspk | last post by:
Hi, I have a input file in the following format , 1-4 5-8 9-10 11 12-14 ------------------------------------ 1000 +500 25 1 250 2000 -520 55 1 25 2500 *525 55 1 125
10
by: eholz1 | last post by:
Hello Members, I am setting up a photo website. I have decided to use PHP and MySQL. I can load jpeg files into the table (medium blob, or even longtext) and get the image(s) to display without...
5
by: yeoj13 | last post by:
Hello, I have a db2load script I'm using to populate a large table. Ideally, my target table is required to have "Not Null" constraints on a number of different columns. I've noticed a ...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.