I have a million record mainframe flat file that I BULK INSERT into a
SQL table with CHAR(fieldlengt h) deined for every column to prevent
import errors.
Once imported I "INSERT INTO ... SELECT
dbo.MyScrubFunc tion(columnN),. .."
My scrub functions will take for example a char(8) YYYYMMDD date field
and return either a valid datetime variable or a NULL for 8-spaces or
8-zeros....or return a MONEY datatype by dividing by 100.
PROBLEM: This is extremely SLOW!
QUESTION: Should I do multiple "UPDATE ...SET" statements back into
CHAR() columns, then let SQL Server do the CHAR() to DATETIME and
MONEY conversions itself? What is the most efficient or recommended
method to transform/scrub imported data?
P.S. I also have to convert low-values and reformat dates which I
also use my own UDFs for.
THANKS