| Stored procs.. [message #256815] |
Mon, 06 August 2007 15:06  |
zzasteven
Messages: 18
Registered: August 2007
|
Junior Member |
|
|
|
I need to be able to insert millions of records into a database really fast.. I was told that if I use a stored proc and pass it arrays and do my insert into the table from that stored proc this would be the fastest way to do it.. I guess my question is this really faster? What would be the best way to insert millions of records fast into a table? I will be importing my data from a flat file which has a complex format of headers and trailers and many detail lines.. I am want to write my reader for this in C# and then call stored procs..
|
|
|
|
|
|
|
|
| Re: Stored procs.. [message #257054 is a reply to message #256866] |
Tue, 07 August 2007 08:30  |
zzasteven
Messages: 18
Registered: August 2007
|
Junior Member |
|
|
Well the way I understand external tables to work is all the data in the file must be put into the same table and of the same type? Here is the problem with that for me.. Lets say I have two tables cat and dog each describe stuff about the dog or cat.. In the file I will get in I can get in cat and dogs
Example of a file:
CatnameCathomeCateyecolor
DognameDogBirthday
So all cat information would be on line 1 and all dog information would be on line 2.. But both will have the data formatted different and they might not have the same fields...
So I cannot map a text file to one logical database table.. Also when I get in the file I will have optional data lines.. Example would be if dog and cat both have a home then I would look for a line that would give me home information as the 3rd line.. So I was trying to find a fast way to import this data into the database..
|
|
|
|
|
|
| Re: Stored procs.. [message #257104 is a reply to message #256815] |
Tue, 07 August 2007 10:32 |
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
|
Senior Member |
|
|
|
Based on what you've said, it sounds like SQL Loader could work. Based on a field in the flat file, you can choose which table to insert the row.
|
|
|
|
|
|
| Re: Stored procs.. [message #257241 is a reply to message #257194] |
Tue, 07 August 2007 19:03 |
zzasteven
Messages: 18
Registered: August 2007
|
Junior Member |
|
|
Ok let me see if i can give you a file example..
EX)
H51 9087thisisahearderforfile
F608976$800thisisaheaderforaaccount
F308976$400thisisaheaderforsubaaccount
F318976$400thisisaheaderforsubaaccount
F618976$800totalforaaccount
H52 9087trailerrecord
We don't normally use the H## records, but sometimes i need too.. The H records also would say that this is the end of this bit of data and you could have different data on the line after the trailer.. As you can tell from my example The file changes on the first 3 letters... However this is not always true...
EX) 2
H51 9087thisisahearderforfile
F708976$800thisisaheaderforaaccount
line1ofaccountinfo
line2ofaccountinfo
line3ofaccountinfo
line4ofaccountinfo
line5ofaccountinfo
line6ofaccountinfo
F718976$800totalforaaccount
H52 9087trailerrecord
In this example line4-6 are not always in the file or not always needed.. But they can never put in line5 without 4.. they will always be in the same order line wise 1 2 3 4 5.. we know how to switch now because of the header information and then we look for the lines if the line count ends we stop and move to the next record..
so it has be able to run these two type of records and also be able to run them if they are in the same file.. We know when they change now by the headers the H##...
So i think that might give you a better idea of how i need to import.. right now we use lookup table and read it line by line.. i was wondering if there was a better way.. We also have to check for double records.. the mainframes screw up and send the same record two times(but i can do that from the tables)..
We don't mix the records like this the mutual funds markets do.. They are running older mainframes and they love to pad records together like this into one file.. So if you want to buy stocks, bonds, etc... all in one Fund they wrap like this or if you are the same client and you want to buy a lot of funds.. plus our system is really old and the system did not always work like this..
In my simple example Cat and dogs are both animals.. It would be like sending in a list of all animals people own in one file..
I am not a expert in oracle... I used to do electronics.. So i guess i could not see how i could use the loader or external tables(without it being a real mess).. I am looking for speed..
thanks for all the help..
|
|
|
|
| Re: Stored procs.. [message #257287 is a reply to message #257241] |
Wed, 08 August 2007 00:40 |
 |
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Post a practical example that we can work with (including create table statement). If you want convert it with your cat and dog analogy.
Please read and follow How to format your posts
Make sure that lines of code do not exceed 80 or 100 characters when you format.
Regards
Michel
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
