I am using Copy command to load data from CSV file to a table using internal Stage.
After loading data I am using below code to get number of rows loaded and failed.
Select * from table(Result_Scan('Copy_Query_ID'))
I am also using below query to get actual failed records:
select * from table(validate("Table_Name",job_id=>'Copy_Query_ID'))
it worked fine few times. But I noticed today that first query shows as below:
Rows_Parsed Rows_Loaded Error_Seen
10000 9600 400
So I was expecting 400 rows in second query result but instead I see 10400 records: All rows once and additional 400 records for some other errors. If all rows are error rows then why are they loaded? Can i not use this queries for this purpose?
Note- In my file I have 6 fields but I am using only 4 of them in Copy and rest two fields I am getting using SYSdate(), may be this is the reason for mismatch?
Copy into table(col1,col2,col3,col4,col5,col6) from ( select $1,$2,$3,$4, sysdate(),'10/20/2020' from %@table)
so I am guessing validate is not looking at my new values for field 5,6 instead it is taking these values from file?