Re: SQL-Loader question, pls

From: Joshua Wright <jwright_at_phish.nether.net>
Date: 1995/12/15
Message-ID: <4artfl$d15_at_news.cic.net>#1/1


Matteo R. diTommaso (ditommaso_at_ebi.ac.uk) wrote:
: > Situation is that every now and then we'll get a request to load an
: > extraordinary amount of data into some tables. All kinds of gotchas occur
: > that stop the load in its tracks - comm errors, dirty data, space, etc.
: > The standard routine is to truncate the tables, turn off foreign keys,
: > load the data, turn the keys on. Any suggestions on how a bunch of real
: > rookies can change this procedure to perhaps break the input files into
: > smaller chunks and and not deviate too much from this routine? It's the
: > truncating that is the first hill to climb.
: >
: > Thanxalot for info. I've been into this stuff for two weeks and can use
: > any kind of help you can offer. preeeeeeeeeeeeeeciate it.
: >
: > --
: > G.T. Jeff Cherer gcherer_at_texas.net
: > Voice: 210-532-7524 SnailMail: 1132 Vanderbilt St. 78210
: > "Comfort the afflicted: Afflict the comfortable."
 

: If I understand the problem - you want to be able to load the data and not
: have to restart when an error causes the job to fail:
 

: Use two tables - once data has successfully loaded move it to the permanent
: table. truncate the temporary table, and restart at the point of failure.
 

: ditommaso_at_ebi.ac.uk

In order to use the insert option on the sqlloader, the table must be empty. In order to truncate the table, there can be no primary/foreign keys referring to the table. So, in order to load a table as a batch job at nght from another OS or something, you would have to :

1 - Drop all constraints on the table
2 - Truncate the table
3 - Re-build all the constraints
4 - Load the table

Which is a pain in the ass. I am doing this nightly as a crontab job right now, and if anyone is really interested, they can mail me and ask for more info.

-Joshua Wright Received on Fri Dec 15 1995 - 00:00:00 CET

Original text of this message