Do you know?

allisonleal

Hello experts. During an INSERT from SELECT clause, I encounter issues like Non-NULL meeting NULL, VARCHAR(20) meeting VARCHAR(36), etc., leading to failed insertions. Is there a method to proceed with the INSERT but divert the rejected data into a secondary table, perhaps using COPY? Thanks!

SruthiA

we dont have that feature for insert select. if you use copy , then you can specify rejected data table clause.

marcothesane

Either export the source table to CSV using the EXPORT TO DELIMITED command, then re-import using COPY, as @SruthiA mentioned.

Or :
Start with INSERT INTO tgt SELECT * FROM src;
For each error you encounter, add a WHERE condition that eliminates the offending rows:
WHERE a_column IS NOT NULL
WHERE OCTET_LENGTH(another_column) <= 20
etc.
Then
CREATE TABLE tgt_errors AS SELECT * FROM src WHERE... ... and add the reverse of the WHERE condition used before.

Tedious and manual - but remains within the database .