SQL Query to modify a column datatype lenght.!!!

UDA
Enthusiast

Re: SQL Query to modify a column datatype lenght.!!!

Hi all....

 

I have been given a DB with hundreds of tables, each with dozens of fields badly created as "float"

 

So, I'm stuck with those data types? can't I change them to Int, numeric, and proper data types?

Teradata Employee

Re: SQL Query to modify a column datatype lenght.!!!

Teradata ALTER TABLE does not allow data type changes that change the size of the storage or the form of the data itself. Float to another numeric type is not allowed.

 

If there are many of these changes to do in a single table, it is best to create a new table with the new desired datatypes, perform an insert select into the new table, drop the old table and rename the new to the old name. This has the nice side effectt of having a recovery copy if the insert select fails eg because the new data types do not accept all the data from the old table. Note that insert select is a very performant operation on Teradata, especially into an empty table.

 

If there are a small number of columns to change in a table, then the strategy mentioned belwo can be used - alter to add a new column with the new type, perform an update to move the data from the old column to the new, alter to drop the old column.

Teradata Employee

Re: SQL Query to modify a column datatype lenght.!!!

It might be a bit late, but I hope this helps. The way I would tackle this is to write a stored procedure that creates a copy of the table, but with the desired column types (and length).

1. Use the column name, and the desired type (and length) as input parameters for the stored procedure.

2. Use DBC.Columns(V) to get the necessary parameters to create a copy of the table with dynamic SQL.

3. Populate the table with an insert select from the original table

4. Delete the original table

5. Rename the copied table

 

It might be a bit of a struggle to build this, but if you'd have to do this for hundreds of tables manually it would take more time.

Good luck

Kind Regards,

Piet