Add a Column to an Existing Table using ALTER TABLE

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