Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Is there any way to include a fixed value in a COPY command

I am trying to use the COPY command to load my exception and rejected data logs into tables. I would also liked to add some fixed and variable values to the copy such as the load run number. The code which loads the log file works:

COPY DSDEPROD.DS_LOAD_REJECTED_DATA FROM '/home/dbadmin/load_home/220/copy_log/D0004001G0015_STAGE_rejected_data.log' DELIMITER '|' TRAILING NULLCOLS;

What I'd like to do, as just one element of this, is load a number, e.g. 100, into a field called LOAD_RUN_NUM. This field isn't part of the log file which is being loaded.

Comments

  • daniel=> create table ben (id int, val varchar(10), constant int);
    CREATE TABLE
    daniel=> copy ben(id, val, constant as 100) from stdin delimiter ' ';
    Enter data to be copied followed by a newline.
    End with a backslash and a period on a line by itself.
    >> 1 foo
    >> 2 bar
    >> \.

    daniel=> select * from ben;
     id | val | constant
    ----+-----+----------
      1 | foo |      100
      2 | bar |      100
    (2 rows)

    daniel=>

  • Great! Thank you Daniel, that works a treat :)

Leave a Comment

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

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.