Add a column with a default value to an existing table in SQL Server

0 votes

How can I add a column with a default value to an existing table in SQL Server 2000 / SQL Server 2005?

Sep 11 in Database by Kithuzzz
• 20,660 points
209 views

1 answer to this question.

0 votes

Syntax:

ALTER TABLE {TABLENAME} 
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL} 
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}
WITH VALUES

Example:

ALTER TABLE SomeTable
        ADD SomeCol Bit NULL --Or NOT NULL.
 CONSTRAINT D_SomeTable_SomeCol --When Omitted a Default-Constraint Name is autogenerated.
    DEFAULT (0)--Optional Default-Constraint.
WITH VALUES --Add if Column is Nullable and you want the Default Value for Existing Records
answered Sep 12 by narikkadan
• 37,660 points

Related Questions In Database

0 votes
0 answers

How to DROP multiple columns with a single ALTER TABLE statement in SQL Server?

In one ALTER TABLE statement, I would ...READ MORE

Sep 2 in Database by Kithuzzz
• 20,660 points
86 views
0 votes
1 answer

How to rename a column in a database table using SQL?

For SQL Server, use sp_rename USE AdventureWorks; GO EXEC sp_rename 'Customers.CustomerTerritory.TerritoryID', ...READ MORE

answered Feb 9 in Database by Neha
• 8,940 points
141 views
0 votes
0 answers

How to find sum of multiple columns in a table in SQL Server 2005?

I have a table Emp which has these rows: Emp_cd ...READ MORE

Aug 19 in Database by Kithuzzz
• 20,660 points
462 views
0 votes
0 answers

How to make a query with group_concat in sql server ?

I am aware that the Group Concat ...READ MORE

Aug 28 in Database by Kithuzzz
• 20,660 points
138 views
0 votes
1 answer

SQL Server CASE .. WHEN .. IN statement

Two forms of CASE statements are getting ...READ MORE

answered Feb 8 in Database by Vaani
• 7,020 points
124 views
0 votes
0 answers

Selecting COUNT(*) with DISTINCT

In SQL Server 2005, I have a ...READ MORE

Feb 14 in Database by Neha
• 8,940 points
125 views
0 votes
1 answer

SQL Server CASE .. WHEN .. IN statement

Try this... SELECT AlarmEventTransactionTableTable.TxnID, ...READ MORE

answered Jun 14 in Others by polo
• 1,480 points
73 views
0 votes
0 answers

Simple DateTime sql query

How do I query the DateTime database ...READ MORE

Aug 12 in Database by Kithuzzz
• 20,660 points
57 views
0 votes
1 answer

Login failed for user 'sa'. The user is not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 18452) in sql 2008

Solution Go to Start > Programs > Microsoft SQL Server > Enterprise Manager. Right-click the SQL ...READ MORE

answered Sep 12 in Database by narikkadan
• 37,660 points
145 views
0 votes
1 answer

Multiple rows to one comma-separated value in Sql Server

Test Data DECLARE @Table1 TABLE(ID INT, Value INT) INSERT ...READ MORE

answered Sep 17 in Database by narikkadan
• 37,660 points
954 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP