Download in db and then check or confirm each line and then load in db

Question

I have a requirement when I have to upload a file in db. The file will contain about 100 thousand records daily and once a month from 8 to 10 million records.

There are also some field level checks that need to be done.

validations: all fields are present, a numerical number contains a valid number, a date contains a valid date, a number in the specified range, does the format of the string match, etc.

There are 3 ways.

1: Upload to temp and then validate - Create a temp table (all string columns), have extra error column - upload all entries to temp table - run validation, populate error column if needed - move valid entries to correct table 

Cons: records must be written twice in db, even the correct ones.

 2: Upload to db directly - upload all entries directly to table - check which entries are not uploaded 

Cons: you will need to read each line even after loading, as good as reading twice

 3: Validate and then Upload - read each line, run all validations on all columns - if valid then write to db 

Cons: reading a file should be slower than bulk loading in db.

I am writing an application in: C # and ASP.NET, DB - Oracle.

Which of the three ways is better?

Answer 1

According to @aF, option 2 with the following addition:
Add a table to which you can discard "invalid" rows. Then run the statement as follows:

 INSERT INTO InvalidData SELECT * FROM InputData WHERE restrictedColumn NOT IN ('A', 'B') OR NOT IS_NUMERIC(numberColumn) -- I'm assuming some version of SQL Server... 

then output the "verified" rows to your actual table, excluding the "invalid" rows:

 INSERT INTO Destination SELECT a.* FROM InputData as a EXCEPTION JOIN InvalidData as b ON b.id = a.id 

INSERT will fail if any (other) "invalid" data is found, but must be available for detection. Then the "invalid" table can be processed for cleaning and reinstalling.

Answer 2

I will go with option 2.

100 thousand lines are peanuts for bulk and query verification.