Anjana H Anjana H - 1 year ago 54
SQL Question

Change the data types and load into different tables in SQL Server

I have a big delimited flat file which has 170 columns. I have loaded this flat file into SQL Server using import/export wizard.

When I load this flat file, all columns are stored as

data type. I need to convert many of these columns into different data types like
etc. and need to load these columns into different tables.

Do I need to use SSIS packages or can I do it in SQL Server?

Can anyone suggest a good procedure to do this?

I really appreciate your valuable time and help.

Answer Source

You can do it in t-sql and as someone who does ETL via SSIS, t-sql, c# etc. I will say this task is strongly preferred in t-sql over SSIS! in SSIS you would have to create a data transformation for all 170 columns and specify type on source as well as destination.....

Do you already know the datatypes you need for the columns? If so and you have the table built it gets easier yet. Just write a really really long insert statement with a select from your original table and in the select CAST or CONVERT from varchar to the desired datatype. The conversion is the tricky part as the insert will fail if the conversion fails do to data in the columns. You can use sys.columns and sys.types to do it dynamically so you don't have 170 different columns a few times.....

This should get you started if you don't want to write all of the data types etc.... Still needs work but a guide.

;WITH cteColumns AS (
       ColumnName =
       ,DataType = CASE
          WHEN LIKE '%CHAR%' THEN ty.Name + '(' + CAST(c.max_length AS VARCHAR(10)) + ')'
          WHEN IN ('DECIMAL','NUMERIC') THEN + '(' + CAST(c.precision AS VARCHAR(10)) + ',' + CAST(c.scale AS VARCHAR(10)) + ')'
       sys.columns c
       INNER JOIN sys.types ty
       ON c.system_type_id = ty.system_type_id
       c.object_id = OBJECT_ID('TableName')

    ColumnName + ' = CAST(source.' + ColumnName + ' AS ' + DataType + ')'
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download