Bulk insert [message #301033] |
Tue, 19 February 2008 00:41  |
nagaa
Messages: 33 Registered: August 2007 Location: chennai
|
Member |
|
|
Hi,
Anybody help me pls...
I need a scripts in pro*c or pl/sql foe the following scenario..
Read data from flat file..
Based on data i have to insert into oracle table(Bulk insert should happen)
For example, if i'm having 10 fields then i have to insert five fields in one table and next five fields in another table..
Pls give me some idea..
Regards,
Nagaa
|
|
|
|
|
Re: Bulk insert [message #301106 is a reply to message #301099] |
Tue, 19 February 2008 03:46   |
S.Rajaram
Messages: 1027 Registered: October 2006 Location: United Kingdom
|
Senior Member |
|
|
Could you be kind enough to be more elaborative so that we could help you or point you in the right direction. Also please read the forum guidelines.
Regards
Raj
|
|
|
|
Re: Bulk insert [message #301125 is a reply to message #301106] |
Tue, 19 February 2008 04:31   |
nagaa
Messages: 33 Registered: August 2007 Location: chennai
|
Member |
|
|
I'm having a flat file which contains 50000 records...
I have to insert that 50000 records into table.(more than one table)
That 50000 records should be inserted at a time that is by using bulk insert..
Process steps:
1. read data from flat file
2. seperate data by using delimter or length as field
3. validate each field
4. insert into table.
Ex: falt file which contains number and date
Number which contains 5 digits
Date which contains 6 digits
In the format 12345DDYYMM
Seperate 12345 as number and DDYYMM as date
validate number and insert into table tbl1
validate date and insert into another table namely as date1
Note: after validate all the records then the insertion should happen that is called as Bulk insert
I think i gave enough information..
|
|
|
Re: Bulk insert [message #301135 is a reply to message #301125] |
Tue, 19 February 2008 05:04   |
S.Rajaram
Messages: 1027 Registered: October 2006 Location: United Kingdom
|
Senior Member |
|
|
Quote: | i didnt get from this link....itcontains more...
|
What contains more ? Could you please elaborate more on the above comment and the link given by Michael explains exactly what you want to achieve.
Also may I know why you insisted Bulk Insert in every single line of your comment ? We are trying to guide to achieve the same using SQL solution and PL/SQL solution.
Regards
raj
|
|
|
Re: Bulk insert [message #301146 is a reply to message #301135] |
Tue, 19 February 2008 05:50   |
nagaa
Messages: 33 Registered: August 2007 Location: chennai
|
Member |
|
|
Raj,
Why i'm going for bulk insert?
because of performance..
If we do insert record by record means it will take time..(i think so..)
If bulk insert means, at one short we insert 50000 records is good performance( i feel..)
With your advice.. i go for that link...
Thanks raj..
I too try the same..
Regards,
Nagadevi
|
|
|
Re: Bulk insert [message #302751 is a reply to message #301135] |
Tue, 26 February 2008 22:24   |
nagaa
Messages: 33 Registered: August 2007 Location: chennai
|
Member |
|
|
i gone through this link...
But the link which gives the deatils of download data from more than one table and upload data into more than one table..
But my ques is download data from spool file(one file) and upload into many table..
pls help me..
Regards,
Nagaa
|
|
|
|
Re: Bulk insert [message #302782 is a reply to message #301125] |
Wed, 27 February 2008 00:34   |
dr.s.raghunathan
Messages: 540 Registered: February 2008
|
Senior Member |
|
|
sir/madam,
so long you provide data in ascii and line sequential (irrespective no of rows), pool it in a single column
write a small procedure use capture the data using
substr(). cursor will be useful on populating data.
ascertain the position of data and your job will be much easier one.
One other option
you shall send a data with position break up
also please provide the table column in which you need data format. I shall write a small cursor/procedure option and execute the procedure. I do not think it is a tougher task.
May be some other hidden facts on conversion on your data.
if so, i shall lift my hand and apologize for my suggestion.
yours
dr.s.raghunathan
My mail id is dr.s.raghunathan@gmail.com
p.s. I am not good at english writing and formating the replies
|
|
|
|
|
|
Re: Bulk insert [message #302867 is a reply to message #302789] |
Wed, 27 February 2008 03:44   |
nagaa
Messages: 33 Registered: August 2007 Location: chennai
|
Member |
|
|
Michel,
Is there any possible to do this task by using any tool which support by oracle..
because in spool file there is lack of record available..
I should not miss anything..
and also i have insert all lack of record into table..
Pls suggest me with any idea..
Regards,
Nagaa
|
|
|
|
|
|
|