Adding a column with a default value to an existing table in SQL Server 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 you can add a column with a default value to an existing table in SQL Server:
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 doesn't 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 ALTER TABLE ADD COLUMN statement
Once you have the table 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 "tblStudent" table 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 insert data into
- The values you want to insert into the table
For example, the following SQL statement inserts a new record into the "tblStudent" table with a 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. Verify 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 of the records in the "tblStudent" table, including the new record with the "age" column set to 18.
Conclusion
In conclusion, adding a column with a default value to an existing table in SQL Server can be accomplished using the ALTER TABLE ADD COLUMN statement. It is a simple and effective way to modify an existing table and add new functionality to your database.
