tsql - How to ALTER multiple columns at once in SQL Server

Question

I need to ALTER the data types of several columns in a table.

For a single column, the following works fine:

ALTER TABLE tblcommodityOHLC
ALTER COLUMN
    CC_CommodityContractID NUMERIC(18,0) 

But how do I alter multiple columns in one statement? The following does not work:

ALTER TABLE tblcommodityOHLC
ALTER COLUMN
    CC_CommodityContractID NUMERIC(18,0), 
    CM_CommodityID NUMERIC(18,0)

Answer 1

This is not possible. You will need to do this one by one. You could:

1. Create a Temporary Table with your modified columns in
2. Copy the data across
3. Drop your original table (Double check before!)
4. Rename your Temporary Table to your original name