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 |