TSQL: Fastest way to convert data_type and update it

Question

My database performance skills are not really good - I could not find any 'good' Google result, so I need your help.

I am trying to convert all columns of a table. All data in this table are datatype varchar.

I do have a reference table which has wrong data but correct meta data like Column_Name, Data_Type etc. ==> So I try to use the table with the correct metadata to convert the table with the correct data. As in the following example, the dynamic script wants to convert a column that should actually be datetime:

IF @Datatype IN ('datetime')
Begin
    set @sqlDate = ('
    Update dbo.'+@Table+' 
    SET '+@Column+' = TRY_CONVERT( datetime, '+@Column+', 105)

    Alter Table dbo.'+@Table+'
    Alter Column '+@Column+' datetime;
    ')

    exec (@sqlDate);    

End

So my goal is to convert a Table like this :

+----------------+----------------+
| Col1 (varchar) | Col2 (varchar) |
+----------------+----------------+
| '01.01.2000'   | '124.5'        |
+----------------+----------------+

To this:

+-------------------------+--------------+
|     Col1(datetime)      | Col2 (float) |
+-------------------------+--------------+
| jjjj-mm-tt hh:mi:ss.mmm |        124.5 |
+-------------------------+--------------+

(based on the correct metadata table)

Do you think its better to first convert data into #TempTable and Update the original Column via the pre-converted #TempTable? Any better solution?

Thanks a lot!

Answer 1

Here's how I would do it.

First, create and populate sample table (Please save is this step in your future questions):

CREATE TABLE Sample
(
    DateTimeColumn varchar(50),
    FloatColumn varchar(50)
);

INSERT INTO Sample(DateTimeColumn, FloatColumn) VALUES ('01.01.2000', '124.5');

Then - Alter the table to add the columns with the correct data type.

ALTER TABLE Sample
    ADD AsDateTime DateTime,
        AsFloat float;

Populate the new columns:

UPDATE Sample
SET AsDateTime =  TRY_CONVERT(datetime, DateTimeColumn, 105),
    AsFloat = TRY_CAST(FloatColumn AS float);

At this point, you should pause and check if you really did get correct values. Once the new columns data is verified, you can delete the old columns

ALTER TABLE Sample
    DROP COLUMN DateTimeColumn;

ALTER TABLE Sample    
    DROP COLUMN FloatColumn;

and rename the new columns:

EXEC sp_rename 'dbo.Sample.AsDateTime', 'DateTimeColumn', 'COLUMN';
EXEC sp_rename 'dbo.Sample.AsFloat', 'FloatColumn', 'COLUMN';

A quick select to verify the change:

SELECT DateTimeColumn, FloatColumn
FROM Sample;

Results:

DateTimeColumn          FloatColumn
2000-01-01 00:00:00     124.5