0

I'm just wondering if anyone has created or heard of a function to guess the appropriate data type of data stored in a SQLServer table as VarChar.

I'm working on an SSIS package that you can point at a directory, and it will loop through and create tables / import data for every CSV that exists it. I'm having trouble with specifying the data types before import, so as a work around I would like to import all the data as VarChar(50) into a temporary table and then run some sort of function to analyze each column for the appropriate data type (int, decimal, float, etc) so I can use that to script the create table and insert statements.

So for example I'd like to be able to point a function or query at this temp table

CREATE TABLE [#Data]
(
[ProductCode] varchar(50),
[ProductName] varchar(50),
[Year] varchar(50),
[Total_volume] varchar(50),
[Total_Quantity] varchar(50),
[PercentSold] varchar(50)
)

to read through the data and determine what data type / length is most appropriate - much like the 'Suggest Data Type' tool in Excel Connection Manager does, only something I can tie into a variable to be done dynamically. It should end up looking something like

CREATE TABLE [Data]
(
[ProductCode] varchar(6),
[ProductName] varchar(11),
[Year] int,
[Total_volume] int,
[Total_Quantity] int,
[PercentSold] decimal(3,2)
)

Any thoughts?

Thanks!

Jamison Pridgen
  • 1
  • 1
  • You could use the built-in checks for most of this. Something like `case when isnumeric(col1) = 1 then case when col1 like '%.%' then 'Decimal' else 'Int' end when ISDATE(col1) = 1 then 'Date' else 'Varchar' end` – APH Jul 28 '15 at 17:14
  • 5
    The import/export wizard in SSMS does datatype guessing. You could ask Microsoft for their algorithm. – Tab Alleman Jul 28 '15 at 17:21
  • 2
    The [Data Profiling Task](https://msdn.microsoft.com/en-us/library/bb895263.aspx) would help. If you are on Enterprise Edition. – Martin Smith Jul 28 '15 at 17:22
  • The problem with this approach is that you're going to run into data like `02116` Oh, that's a number so we'll declare it as a an int and then the fine people of Boston are going to hate you for claiming their zip code is 2116. That leading zero is significant. [IsNumeric](http://stackoverflow.com/q/7400318/181965) can return curious results. Do you know your percents won't have an actual % sign? – billinkc Jul 28 '15 at 23:21

0 Answers0