Adding a column with a default value to an existing SQL Server table can be achieved using the ALTER TABLE ADD COLUMN statement. This statement allows you to add a new column to an existing table with a default value specified for the column.
Here’s how to add a column with a default value to an existing SQL Server table:
1. Create the table in SQL Server
To add a column to an existing table in SQL Server, you must first create the table if it does not exist. Use the CREATE TABLE statement to create the table, specifying the column names and data types.
For example, the following SQL statement creates a table named “tblStudent” with two columns, “id” and “name”:
CREATE TABLE tblStudent ( id INT PRIMARY KEY, name VARCHAR(50) );
2. Use the ALTER TABLE ADD COLUMN statement
After the table is created, you can use the ALTER TABLE ADD COLUMN statement to add a new column to the table. This statement takes three arguments:
- The name of the table you want to modify
- The name of the new column you want to add
- The data type of the new column
For example, the following SQL statement adds a new column named “Age” to the table “tblStudent” with a default value of 18:
ALTER TABLE tblStudent ADD COLUMN age INT DEFAULT 18;
3. Insert values into the new column
Now that you have added the new column to the table, you can insert values into the column using the INSERT INTO statement. The INSERT INTO statement takes two arguments:
- The name of the table you want to add data to
- The values you want to add to the table
For example, the following SQL statement adds a new record to the “tblStudent” table with the value of “Nikunj Satasiya” for the “Name” column and a value of 18 for the “Age” column:
INSERT INTO tblStudent (name, age) VALUES ('Nikunj Satasiya', 18);
4. Validate the new column and values
You can verify that the new column and values have been added to the table by running a SELECT statement:
SELECT * FROM tblStudent;
This will display all records in the “tblStudent” table, including the new record with the “Age” column set to 18.
Summary
In summary, adding a column with a default value to an existing SQL Server table can be done using the ALTER TABLE ADD COLUMN statement. This is a simple and effective way to modify an existing table and add new functionality to your database.
