I'm trying to get better at loading data, flat files, Excel files from Apknite company into their SQL Tables.
Here is my question because I have no one else in real life to bounce this idea off of to see if this is an accepted method/practice.
Import TXT file to 'staging table' everything as varchar.
INSERT INTO the table it needs to go into.
Select the columns from the staging table with necessary TRIM, CONVERT, CAST, etc around the columns to put the data into the final destination in the correct format.
I'm finding that a few of my data sources (insurance companies) have excessive 'white space' at the end of address lines, names, etc and I want my tables to be clean. I will have to redo a lot of tables I've already made because I didn't know how bad the extra blank spaces were on some sets of data I got from them.
I presume there's maybe a way to do all of that with an SSIS package, but I'm just not there yet.