Home » SQL & PL/SQL » SQL & PL/SQL » Bulk insert (Oracle 9i version)
Bulk insert [message #301033] Tue, 19 February 2008 00:41 Go to next message
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 #301035 is a reply to message #301033] Tue, 19 February 2008 00:45 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
External table.
INSERT ALL/FIRST.

Regards
Michel
Re: Bulk insert [message #301099 is a reply to message #301035] Tue, 19 February 2008 03:36 Go to previous messageGo to next message
nagaa
Messages: 33
Registered: August 2007
Location: chennai
Member
i didnt get from this link....itcontains more...
Re: Bulk insert [message #301106 is a reply to message #301099] Tue, 19 February 2008 03:46 Go to previous messageGo to next message
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 #301108 is a reply to message #301099] Tue, 19 February 2008 03:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
SQL> create table t (c1 integer, c2 integer);
SQL> insert into t values (1,2);
SQL> create table t1 (c1 integer);
SQL> create table t2 (c2 integer);
SQL> insert all
  2  into t1 values (c1)
  3  into t2 values (c2)
  4  select * from t
  5  /
SQL> select * from t1;
        C1
----------
         1
SQL> select * from t2;
        C2
----------
         2

Regards
Michel
Re: Bulk insert [message #301125 is a reply to message #301106] Tue, 19 February 2008 04:31 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #302772 is a reply to message #302751] Wed, 27 February 2008 00:23 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
The answer is in the first line of my first answer.
I didn't post a link but you can easely search for these 2 words here or on Oracle doc.

Regards
Michel
Re: Bulk insert [message #302782 is a reply to message #301125] Wed, 27 February 2008 00:34 Go to previous messageGo to next message
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 #302789 is a reply to message #302782] Wed, 27 February 2008 00:45 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
You can do it in SQL, don't use PL/SQL.

Regards
Michel
Re: Bulk insert [message #302862 is a reply to message #302789] Wed, 27 February 2008 03:40 Go to previous messageGo to next message
nagaa
Messages: 33
Registered: August 2007
Location: chennai
Member
sorry michel,

i didn't get u...
Re: Bulk insert [message #302866 is a reply to message #302862] Wed, 27 February 2008 03:43 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
On which part?

Regards
Michel
Re: Bulk insert [message #302867 is a reply to message #302789] Wed, 27 February 2008 03:44 Go to previous messageGo to next message
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
Re: Bulk insert [message #302868 is a reply to message #302867] Wed, 27 February 2008 03:44 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
http://www.oracle.com/pls/db102/search?remark=quick_search&word=external+table&tab_id=&format=ranked

Regards
Michel
Re: Bulk insert [message #302871 is a reply to message #302866] Wed, 27 February 2008 03:49 Go to previous messageGo to next message
nagaa
Messages: 33
Registered: August 2007
Location: chennai
Member
i didn't get u in the message #302772


Re: Bulk insert [message #302881 is a reply to message #302871] Wed, 27 February 2008 04:22 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Don't you know how to use quoting?

What don't you understand "first line"? "first answer"? 2 words of first line of first answer?
Now link is in my previous post.

Regards
Michel
Re: Bulk insert [message #302893 is a reply to message #302881] Wed, 27 February 2008 04:52 Go to previous message
nagaa
Messages: 33
Registered: August 2007
Location: chennai
Member
Sorry Michel,

i got your answer at first itself..
I forgot to reply..

Previous Topic: An update statement required
Next Topic: Predefined Exception
Goto Forum:
  


Current Time: Thu Feb 13 03:26:49 CST 2025