Do you know?

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!


  • Options
    SruthiASruthiA Vertica Employee Administrator

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

  • Options
    marcothesanemarcothesane - Select Field - Administrator

    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
    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 .

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file