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.
Is there any way to include a fixed value in a COPY command
+1+1daniel=> 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=>
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=>
+1Great! Thank you Daniel, that works a treat 
Sign up
Already have an account? Login
Welcome to the Rocket Forum!
Please log in or register:
Employee Login | Registration Member Login | RegistrationEnter your E-mail address. We'll send you an e-mail with instructions to reset your password.
77 4th Avenue
Waltham, MA 02451 USA
