How to ALTER multiple columns at once in SQL Server

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)

Solution 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

Solution 2:

Doing multiple ALTER COLUMN actions inside a single ALTER TABLE statement is not possible.

See the ALTER TABLE syntax here

You can do multiple ADD or multiple DROP COLUMN, but just one ALTER COLUMN.

Solution 3:

As others have answered, you need multiple ALTER TABLE statements.
Try following:

ALTER TABLE tblcommodityOHLC alter column CC_CommodityContractID NUMERIC(18,0);
ALTER TABLE tblcommodityOHLC alter column CM_CommodityID NUMERIC(18,0);

Solution 4:

The following solution is not a single statement for altering multiple columns, but yes, it makes life simple:

1. Generate a table's CREATE script.

2. Replace CREATE TABLE with ALTER TABLE [TableName] ALTER COLUMN for first line

3. Remove unwanted columns from list.

4. Change the columns data types as you want.

5. Perform a Find and Replace… as follows:

   1. Find: NULL,
   2. Replace with: NULL; ALTER TABLE [TableName] ALTER COLUMN
   3. Hit Replace button.

6. Run the script.

Hope it will save lot of time :))