how to discard rows of a text file which does not satisfies foreign key constraint and continue with the rest

[kutty]

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_ex ec.dat'
INTO TABLE CS_COMMON_SHELL _EXEC_REG FIELDS TERMINATED BY '#'
(reg_id,func_ad dress);

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_SHEL L_EXEC_REG_ibfk _2` FOREIGN KEY (`func_address` )
REFERENCES `CS_COMMON_SHEL L_EXEC` (`func_address` ))

Thanks
Devi.

[Jerry Gitomer]

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_ex ec.dat'
INTO TABLE CS_COMMON_SHELL _EXEC_REG FIELDS TERMINATED BY '#'
(reg_id,func_ad dress);

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_SHEL L_EXEC_REG_ibfk _2` FOREIGN KEY (`func_address` )
REFERENCES `CS_COMMON_SHEL L_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

[kutty]

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. 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_ex ec.dat'
INTO TABLE CS_COMMON_SHELL _EXEC_REG FIELDS TERMINATED BY '#'
(reg_id,func_ad dress);

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_SHEL L_EXEC_REG_ibfk _2` FOREIGN KEY (`func_address` )
REFERENCES `CS_COMMON_SHEL L_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

[Jeff North]

On 27 Sep 2006 05:04:45 -0700, in mailing.databas e.mysql "kutty"
<sa*****@gmail. com>
<11************ **********@b28g 2000cwb.googleg roups.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_ex ec.dat'
| INTO TABLE CS_COMMON_SHELL _EXEC_REG FIELDS TERMINATED BY '#'
| (reg_id,func_ad dress);
|
| 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_SHEL L_EXEC_REG_ibfk _2` FOREIGN KEY (`func_address` )
| REFERENCES `CS_COMMON_SHEL L_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******@yourpa ntsyahoo.com.au : Remove your pants to reply
---------------------------------------------------------------

[kutty]

Hi

Thanks Jerry and Jeff for your valuable suggestion. I will try this
approach.

Thanks
Kutty.

Jeff North wrote:

On 27 Sep 2006 05:04:45 -0700, in mailing.databas e.mysql "kutty"
<sa*****@gmail. com>
<11************ **********@b28g 2000cwb.googleg roups.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_ex ec.dat'
| INTO TABLE CS_COMMON_SHELL _EXEC_REG FIELDS TERMINATED BY '#'
| (reg_id,func_ad dress);
|
| 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_SHEL L_EXEC_REG_ibfk _2` FOREIGN KEY (`func_address` )
| REFERENCES `CS_COMMON_SHEL L_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******@yourpa ntsyahoo.com.au : Remove your pants to reply
---------------------------------------------------------------

[Devi]

Hi All,

This is not working for me..
This approach is fine for smaller tables.But if i try to insert data
from the temporary table to the child table
Error occurs:
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting
transaction

can any one suggest me a solution?

Thanks
Kutty

kutty wrote:

Hi

Thanks Jerry and Jeff for your valuable suggestion. I will try this
approach.

Thanks
Kutty.

Jeff North wrote:

On 27 Sep 2006 05:04:45 -0700, in mailing.databas e.mysql "kutty"
<sa*****@gmail. com>
<11************ **********@b28g 2000cwb.googleg roups.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_ex ec.dat'
>| INTO TABLE CS_COMMON_SHELL _EXEC_REG FIELDS TERMINATED BY '#'
>| (reg_id,func_ad dress);
>|
>| 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_SHEL L_EXEC_REG_ibfk _2` FOREIGN KEY (`func_address` )
>| REFERENCES `CS_COMMON_SHEL L_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******@yourpa ntsyahoo.com.au : Remove your pants to reply
---------------------------------------------------------------