Add a Column to an Existing Table using ALTER TABLE

September 4, 2021

Here is the syntax to add a column to an existing table using ALTER TABLE:

ALTER TABLE table_name
ADD new_column_name data_type

Next, you’ll see how to apply the above syntax using a simple example.

Add a Column to an Existing Table using ALTER TABLE

Suppose that you have a table called products.

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

product_id product_name
1 Desktop Computer
2 Laptop
3 Tablet
4 Monitor
5 Printer

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

product_id product_name price
1 Desktop Computer 800
2 Laptop 1200
3 Tablet 200
4 Monitor 350
5 Printer 150

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

Let’s 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 the ‘price’ column is currently NULL:

product_id product_name price
1 Desktop Computer NULL
2 Laptop NULL
3 Tablet NULL
4 Monitor NULL
5 Printer 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:

product_id product_name price
1 Desktop Computer 800
2 Laptop 1200
3 Tablet 200
4 Monitor 350
5 Printer 150

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, let’s 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

Let’s 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:

product_id product_name price brand
1 Desktop Computer 800 NULL
2 Laptop 1200 NULL
3 Tablet 200 NULL
4 Monitor 350 NULL
5 Printer 150 NULL

Let’s now update the values under the ‘brand’ column (since we are dealing with strings, you’ll need to 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:

product_id product_name price brand
1 Desktop Computer 800 A
2 Laptop 1200 B
3 Tablet 200 C
4 Monitor 350 D
5 Printer 150 E