May 5, 2009 at 8:01 am
hello i have a bit column in my table, i need to make sure it has the default set to 0 in case null is inserted. can someone help me the easiest way to do this?
May 5, 2009 at 8:07 am
Does your column currently have data in it?
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
May 5, 2009 at 9:32 am
Well, its kind of slow today, so... Probably the easiest way is to open EM, right click the table, choose 'Design table', highlight the column, and populate 0 in the default field at the bottom, then hit save. You could also try something like this.
IF OBJECT_ID('TempDB..#temp','u') IS NOT NULL
DROP TABLE #temp
CREATE TABLE #temp
(
DataCol VARCHAR(20),
BitCol BIT NULL
)
INSERT INTO #temp (DataCol)
SELECT 'Blue' UNION ALL
SELECT 'Orange' UNION ALL
SELECT 'Green'
SELECT
*
FROM #temp
ALTER TABLE #temp
ADD CONSTRAINT DF_#temp_BitCol DEFAULT(0) FOR BitCol
INSERT INTO #temp (DataCol)
SELECT 'Brown' UNION ALL
SELECT 'Slate'
SELECT
*
FROM #temp
UPDATE #temp
SET BitCol = 0
WHERE BitCol IS NULL
SELECT
*
FROM #temp
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
May 5, 2009 at 1:40 pm
what about for the future inserts into the table?, is there a way to add a constraint if the column is null when a new row is entered then set default to 0
May 5, 2009 at 1:51 pm
In addition to what Greg mentioned above you can also set the column to not allow nulls in the designer. I am not sure it is completely necessary but that should guarantee that any attempt to insert NULL will result in the default 0
Regards,
Jason P. Burnett
Senior DBA
May 6, 2009 at 8:30 am
thank you!
May 6, 2009 at 8:44 am
DBA (5/5/2009)
what about for the future inserts into the table?, is there a way to add a constraint if the column is null when a new row is entered then set default to 0
If you try the code I posted, you will see the answer to your question.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply