before loading csv to SQL is there a way to validate against table schema to catch potential data issues and row number
create a good file(records pass) and bad file(for review).
I wouldn't do a separate pre-validation because it's a waste of time. No matter what, you have to scan the CSV file. That scan might as well also double as a load. If the load has no errors, then you're all done. If the load has errors, then you've still only done a single scan.
There are various thoughts on how to do this. Personally, I create a table with just exactly the correct datatypes, set error tolerance to 2 billion rows, set a BULK INSERT command (in T-SQL) to sequester the bad rows and errors, and do the load into a table.
is pronounced "ree-bar
" and is a "Modenism
" for R
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
"If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
"Change is inevitable... change for the better is not."
When you put the right degree of spin on it, the number 3|8
is also a glyph that describes the nature of a DBAs job. 😉
How to post code problems
Create a Tally Function (fnTally)