Add Column to an Existing Table using ALTER TABLE

To add a column to an existing table using ALTER TABLE:

ALTER TABLE table_name
ADD new_column_name data_type

The Example

Suppose that you have a table called products.

The ‘products’ table currently contains the following columns and data:

The ultimate goal is to add an additional column called ‘price‘ with the following values (as highlighted in yellow):

You can then use the template below in order to add the ‘price‘ column:

ALTER TABLE table_name
ADD new_column_name data_type

For our example:

• The table_name is: products
• The new_column_name is: price
• The data_type is: int

So the complete ALTER TABLE query for our example would look as follows:

ALTER TABLE products
ADD price int

Once you run the above query, the new column would be added:

Commands completed successfully

Run the following SELECT query to see the results:

SELECT * FROM products

Notice that the ‘price‘ column was added at the end of the table, where each value under this column is currently NULL:

You can then use an UPDATE query to replace those NULL values with the actual prices:

UPDATE products
SET price = 
	CASE WHEN product_id = 1 THEN 800
	     WHEN product_id = 2 THEN 1200
	     WHEN product_id = 3 THEN 200
	     WHEN product_id = 4 THEN 350
	     WHEN product_id = 5 THEN 150
	END

Run the above UPDATE query.

Then, rerun the following SELECT query to see the new values:

SELECT * FROM products

You’ll now see the actual prices:

So far, you have seen how to add a column with integer values.

Alternatively, you can add a column with string/text values.

For example, add another column called ‘brand‘ that contains strings. The data type for this column would be nvarchar(50):

ALTER TABLE products
ADD brand nvarchar(50)

Run the above query, and the new ‘brand‘ column would be added:

Commands completed successfully

Rerun the following SELECT query to see the results:

SELECT * FROM products

As you can see, the ‘brand‘ column was added at the end of the table with NULL values:

Update the values under the ‘brand‘ column (since we are dealing with strings, place the brand values inside quotes):

UPDATE products
SET brand = 
	CASE WHEN product_id = 1 THEN 'A'
	     WHEN product_id = 2 THEN 'B'
	     WHEN product_id = 3 THEN 'C'
	     WHEN product_id = 4 THEN 'D'
	     WHEN product_id = 5 THEN 'E'
	END

Finally, run the SELECT query below in order to see the results:

SELECT * FROM products

You’ll see the brand values at the end of the table: