Setting Default Value on Table 1

[bajo71]

Hello,
I've set the default value for a particular field in table properties as ='7' -meaning if there is no value populate with 7. However, nothing shows up when I view the rows or recordset through stored procedure.

Thanks for your help

 

[gmmastros]

Defaults are only used if you don't specify the field during the insert.

Take a look at this code.

[COLOR=blue]Create[/color] [COLOR=blue]Table[/color] #Temp(Id [COLOR=blue]Int[/color], Data [COLOR=blue]VarChar[/color](10) [COLOR=blue]Default[/color] [COLOR=red]'7'[/color])

[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] #Temp(Id) [COLOR=blue]Values[/color](1)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] #Temp(Id, Data) [COLOR=blue]Values[/color](2,[COLOR=red]''[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] #Temp(Id, Data) [COLOR=blue]Values[/color](3,NULL)

When you select the records from the temp table, you get this...

[tt][blue]Id Data
----------- ----------
1 7
2
3 NULL

(3 row(s) affected)
[/blue][/tt]

The first insert ignored the Data column completely, and the default was used. The second and 3rd inserts used the column, so it got the value specified.

Does this make sense?

-George

"the screen with the little boxes in the window." - Moron

 

[AlexCuse]

Default value is only applied when new data is inserted.

Try an update query if you want to replace all missing values with '7' (be careful though!)

Hope this helps,

Alex

[small]----signature below----[/small]
I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom

 

[SQLDenis]

Here si the teaser guys: thread183-1400661

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[gmmastros]

I missed something from my example. It's another way to get the default value in to a table.

Create Table #Temp(Id Int, Data VarChar(10) Default '7')

Insert Into #Temp(Id) Values(1)
Insert Into #Temp(Id, Data) Values(2,'')
Insert Into #Temp(Id, Data) Values(3,NULL)
Insert Into #Temp(Id, Data) Values(4,[!]Default[/!])

Now, the output will look like this...

[tt][blue]
Id Data
----------- ----------
1 7
2
3 NULL
4 7

(4 row(s) affected)
[/blue][/tt]

-George

"the screen with the little boxes in the window." - Moron