Is there a way to auto-assign data types to my table?
Hey guys, quick question... I just imported a large .csv file, that has about 60 columns, and about 300,000 rows... In order to get the import to work properly I just set all columns to data type string and field size of 500. When I tried to do it the other ways, I just got errors every couple hundred rows about some sort of anomaly... So now I have this table, is there anyway that I can either copy this table and auto-detect data types or update these data types automatically? Thanks
Usually the setting of the datatypes is done at the time of import. SSIS and other tools, such as Log Parser, can be set to review the first N rows of the sourcefile in order to attribute a datatype to the new (destination) table. It would appear from what you say that the source data does have anomalies in it if you cannot import the data successfully with more restrictive data type settings. Is this a characteristic of the data or is it's integrity fundamentally out of whack? My next step from your position would be to analyse the data, column by column, making data fixes if appropriate or identifying issues as they are encountered. Once you have worked on all the columns you can use TSQL to move the data from your current table to one with a better schema. You may well be using ISDATE (
http://msdn.microsoft.com/en-us/library/ms187347.aspx), ISNUMERIC (
http://msdn.microsoft.com/en-us/library/ms186272.aspx) and so on to identify records that fit, or fail to fit, your expectations. Once you have the datatypes that you want to have applied then create a new table accordingly and then you can use a simple `INSERT` statement to move the data. You can convert data during this phase or simply refine the data quality in the current table prior to the transfer. Sadly there is no way to automatically assess the data for the appropriate datatypes where the data is in some way inconsistent as you delve deeper into the recordset.