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.
Notes:
Optional Constraint Name:
If you leave out CONSTRAINT D_SomeTable_SomeCol then SQL Server will autogenerate
a Default-Contraint with a funny Name like: DF__SomeTa__SomeC__4FB7FEF6
Optional With-Values Statement:
The WITH VALUES is only needed when your Column is Nullable
and you want the Default Value used for Existing Records.
If your Column is NOT NULL, then it will automatically use the Default Value
for all Existing Records, whether you specify WITH VALUES or not.
How Inserts work with a Default-Constraint:
If you insert a Record into SomeTable and do not Specify SomeCol's value, then it will Default to 0.
If you insert a Record and Specify SomeCol's value as NULL (and your column allows nulls),
then the Default-Constraint will not be used and NULL will be inserted as the Value.
Notes were based on everyone's great feedback below.
Special Thanks to:
@Yatrix, @WalterStabosz, @YahooSerious, and @StackMan for their Comments.
You can use OPENROWSET for this. Have a look. I've also included the sp_configure code to enable Ad Hoc Distributed Queries, in case it isn't already enabled.
CREATE PROC getBusinessLineHistory
AS
BEGIN
SELECT * FROM sys.databases
END
GO
sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO
SELECT * INTO #MyTempTable FROM OPENROWSET('SQLNCLI', 'Server=(local)\SQL2008;Trusted_Connection=yes;',
'EXEC getBusinessLineHistory')
SELECT * FROM #MyTempTable
Best Solution
Trailing Underscore
Add a trailing underscore:
GROUP_The SQL spec explicitly promises† that no keyword will ever have a trailing underscore. So you are guaranteed that any naming you create with a trailing underscore will never collide with a keyword or reserved word.
I name all my columns, constraints, etc. in the database with a trailing underscore. Seems a bit weird at first, but you get used to seeing it. Turns out to have a nice side-effect: In all the programming as well as notes and emails, when I see the trailing underscore I know the context is the database as opposed to a programming variable or a business term.
Another benefit is peace-of-mind. Such a relief to eliminate an entire class of possible bugs and weird problems due to keyword collision. If you are thinking, "No big deal - what's a few SQL keywords to memorize and avoid", think again. There are a zillion keywords and reserved words, a zillion being over a thousand.
The answer by Shiva is correct as well: Adding quotes around the name,
"GROUP", does solve the problem. The downside is that remembering to add those quotes will be tiresome and troublesome.Further tip: For maximum compatibility across various SQL databases, do your naming in all lowercase. The SQL spec says that all names should be stored in uppercase while tolerating lowercase. But unfortunately some (most?) databases fail to follow the spec in that regard. After hours of study of various databases, I concluded that all-lowercase gives you maximum portability.
So I actually suggest you name your column:
group_Multiple word names look like this:
given_name_anddate_of_first_contact_† I cannot quote the SQL spec because it is copyright protected, unfortunately. In the SQL:2011 spec, read section 5.4 Names and identifiers under the heading Syntax Rules item 3, NOTE 111. In SQL-92 see section 5.2, item 11. Just searching for the word
underscorewill work.