Solved

Find DataTypes of a staging table

Posted on 2014-03-31
5
208 Views
Last Modified: 2014-05-14
I have a staging table that has been imported from excel, every column is varchar(255).  I need to move the data into a new table that has the correct datatypes for each column.  I need a best guess at least on the column datatype and we never know how many columns the table is going to be, we only know it will be called staging_[something].  The datatypes I need to check for are int, datetime, float, decimal, varchar, bit.  What is the best way to do this in T-SQL.  Thanks
0
Comment
Question by:nutnut
  • 2
  • 2
5 Comments
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39966999
Profiling the columns for int, float, decimal and bit is fairly straightforward.

Checking datetime vs general varchar could be very difficult.  Do you have some type of restrictions on how the date/datetime could be entered?
0
 

Author Comment

by:nutnut
ID: 39967316
Yes we do we have maybe 5 different common ways. I was thinking as long as the bulk of the column have a 13,14,15 or 16 in its a date because all dates will be from 2013,14,15 and 16?
0
 
LVL 35

Expert Comment

by:David Todd
ID: 39967778
Hi,

Can you look at Excel and from there guess at the datatype?

Regards
  David
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
ID: 39967954
I've got very limited time now, but I think the code below will provide you a decent start.  You seemed to have a better handle on testing for date than I did -- I would want specific patterns -- so I left that for you.

My code presumes you want to look at *every* row before deciding on the type.  If there are too many rows, you'll probably want to add a "TOP (nnnn)" expression.

I've only coded column1, but column2 would follow exactly the same pattern, and so on for column3, column4, etc..

I'd SELECT these results into a temp table, either one row for all columns or one row per column, and analyze the types there.

The basic analysis would be something like:
  -- if total_count = bit_ok_count and sign_location_max = 0 and float_location_final_max = 0  then  type = bit
  -- if total_count = int_ok_count and sign_location_max IN (0, 1) and float_location_final_max = 0  then  type = int
  --  if total_count = decimal_ok_count and sign_location_max IN (0, 1) and float_location_final_max = 0  then  type = decimal
  --  if total_count = datetime_ok_count  then  type = datetime
  -- else  type = varchar.
 
SELECT
    SUM(1) AS Total_Count,
    MIN(sign_location) AS sign_location_min,
    MAX(sign_location) AS sign_location_max,
    MIN(float_location_final) AS float_location_final_min,
    MAX(float_location_final) AS float_location_final_max,
    SUM(CASE WHEN column1_mod_final NOT LIKE '%[^0123456789]%' THEN 1 ELSE 0 END) AS int_ok_count,
    SUM(CASE WHEN column1_mod_final NOT LIKE '%[^0-9.]%' AND column1_mod_final NOT LIKE '%.%.%' THEN 1 ELSE 0 END)
        AS decimal_ok_count,
    SUM(CASE WHEN column1_mod_final IN ('0', '1', 'False', 'True') THEN 1 ELSE 0 END) AS bit_ok_count
    --add your date testing logic here AS datetime_ok_count
FROM ( --dbo.staging_table1
    SELECT '12e+5' AS column1
) AS staging_table1
CROSS APPLY (
    SELECT PATINDEX('%[+-]%', column1) AS sign_location,
        PATINDEX('%[DE]%', column1) AS float_location_prelim
) AS ca10
CROSS APPLY (
    SELECT SUBSTRING(column1, sign_location, 1) AS [sign]
) AS ca20
CROSS APPLY (
    SELECT CASE WHEN sign_location > 0 THEN STUFF(column1, sign_location, 1, '') ELSE column1 END AS column1_mod_1
) AS ca30
CROSS APPLY (
    SELECT CASE WHEN float_location_prelim > 0 AND
        SUBSTRING(column1_mod_1, float_location_prelim + 1, 99) NOT LIKE '%[^0123456789]%' AND
        (sign_location = 0 OR sign_location > float_location_prelim) THEN float_location_prelim ELSE 0 END
        AS float_location_final
) AS ca4
CROSS APPLY (
    SELECT CASE WHEN float_location_final > 0 THEN STUFF(column1_mod_1, float_location_final, 1, '')
        ELSE column1_mod_1 END AS column1_mod_final
) AS ca6
0
 

Author Closing Comment

by:nutnut
ID: 40065392
Great answer thanks!!
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now