On 27 Sep 2006 05:04:45 -0700, in mailing.database.mysql "kutty"
<sa*****@gmail.com>
<11**********************@b28g2000cwb.googlegroups .comwrote:
>| Hi Jerry,
|
| Thanx a lot..
|
| but..I am loading a huge volume of data (min 2 GB). Two load operations
| might increase the loading time.
It might also decrease the load time!
Loading imported data into a non-index, non constrained table is
pretty quick. Then taking that data and only moving the relevant data
is also pretty quick.
Loading imported data into a temp table has the benefits of:
if the import data is corrupted then it is easy to restart the process
without disturbing your live data.
further manipulation of the data can be carried out prior to transfer
to the live tables.
business rules can be applied to the data prior to going live
once the valid data has been processed you can then see what is left
behind and then make a value judgement if this data can be discarded
or incorporated into the live data.
>| I dont want that to happen.. is there
| any possible way to discard the rows which doent satify the constraint?
| Oracle support this. It creates a discard file..
|
| Thanks
| Kutty
|
| Jerry Gitomer wrote:
| kutty wrote:
| Hi All,
|
| I am loading data to a child table from a text file. the text files
| also contains data not referenced by parent key. while loading the data
| if one row fails to satisfies the constraint everything is getting
| rollback..
|
| plz suggest me something.. which will help me to discard the
| unsatisfied rows and continue with the rest..
|
| My Query:
| LOAD DATA CONCURRENT INFILE
| '/remote/srm172/saranya/SOURCECOV/common_shell/output/data/common_shell_exec.dat'
| INTO TABLE CS_COMMON_SHELL_EXEC_REG FIELDS TERMINATED BY '#'
| (reg_id,func_address);
|
| Error Msg:
| ERROR 1452 (23000): Cannot add or update a child row: a foreign key
| constraint fails (`sourcecov/CS_COMMON_SHELL_EXEC_REG`, CONSTRAINT
| `CS_COMMON_SHELL_EXEC_REG_ibfk_2` FOREIGN KEY (`func_address`)
| REFERENCES `CS_COMMON_SHELL_EXEC` (`func_address`))
|
| Thanks
| Devi.
|
| One approach is to load a temporary table which has no
| constraints and then insert from the temporary to the child only
| those rows where the foreign key constraint is satisfied.
| >
| Advantages are that minimal changes are required to your
| existing load program and the insert can be handled with a
| simple INSERT INTO child (SELECT * FROM temp WHERE constraints
| are met); You may have to play around with the syntax -- I can
| never remember if you need that parenthesis and/or an AS before
| the SELECT.
| >
| HTH
| Jerry
---------------------------------------------------------------
jn******@yourpantsyahoo.com.au : Remove your pants to reply
---------------------------------------------------------------
