my question: Importing delimited files into SQL server
The point of this question is to say that I do not want to specify the types and sizes for all the columns manaully. There are too many, and it is error prone.
I wished to use the suggest types feature, but it is (apparently) known to not work.
What can I do?
That is my question. Should I edit my question to more strongly reflect that? Is that inheritly unclear?
Two things:
1) It's unclear because we don't know what's breaking because we don't know your data. Apparently you don't either. You probably have things that look like numbers and text in the same column, so it's guessing there's numbers and blows up on text. That has happened before to me.
2) Tough. You're gonna have to know about your data before you import it. That means defining types and everything else. I just recently imported about 60GB of data into my system from about 160 files and procedures to expand the data, so I can say that while defining a bunch of working tables (at least 1 for each file, some needed several cleanup processes, so an additional table per process) is a pain in the ass, it's necessary.
I don't see how manually defining columns is error prone. Open the file in Excel (it does delimited work as well) and see how wide each column is and work with it from there. You're really trying to make this too hard.
Lastly:
I didn't understand from your question that the issue was "apparently suggested types isn't known to work, how can I work around that". But honestly, the way to work around that is to specify the column types ahead of time.
