Way to create a table from a flat file in SQL Server without knowing datatypes?

Question

Is there a way to load a flat file into SQL Server as a new table without knowing the data types of the columns in the file?

I know one can use BULK INSERT to load flat file data, but a target table must first be created that matches the datatypes in the incoming file. OPENROWSET requires the creation of a format file that specifies the incoming data types. Is there a way to get SQL Server to automatically introspect the file, determine the datatypes and load the data in one shot?

Answer 1

No: it all has to done at design time (eg SSIS) or known in advance (eg BULK INSERT).

If you really don't know the data you'll get then use nvarchar with some processing afterwards to work out what you have.

I'd use this anyway in a staging table if I didn't 100% trust the data source.

I do find it odd you don't know what you'll get though... what can you do with it?