Solved

Find DataTypes of a staging table

Posted on 2014-03-31
5
209 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:Scott Pletcher
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:
Scott Pletcher 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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
T-SQL: Creating Records Where There Are None2 - The Sequel 6 27
query execution hang 5 29
VB.NET 2008 - SQL Timeout 9 24
SQL Server Import/Error Wizard error 12 17
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

776 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