Default values in tables

Hello all,
I thought that I knew how to do this, but I must be having a mental
block. I have a SQL table, populated with 14,000 records. I have
several int columns which I need to change the default value to 0
(zero), and a datetime column which I would like to default to todays
date and current time. I have gone into design view for each of the
int columns and I have simply entered 0 in the "Default value" box.
For the datetime column, I have changed the Default Value to
getdate().
I have gone to enter a new record in the table, and none of the
defaults work. All still default to <null>.
Any ideas why this is not working? Does it only work on a table
without existing data?
Thanks
ColinBobby
It does work
CREATE TABLE #Test (c1 datetime DEFAULT GETDATE(),c2 INT DEFAULT 0)
INSERT INTO #Test DEFAULT VALUES
SELECT * FROM #Test
Check out that you have created default constraints
SELECT scobj.name, cols.name
FROM sysconstraints sc
INNER JOIN sysobjects scobj
ON sc.constid = scobj.id
AND sc.id=OBJECT_ID('tblname')
INNER JOIN syscolumns cols
ON sc.id = cols.id
AND sc.colid = cols.colid
GO
"Bobby" <bobby2@.blueyonder.co.uk> wrote in message
news:1174466348.874762.198710@.e1g2000hsg.googlegroups.com...
> Hello all,
> I thought that I knew how to do this, but I must be having a mental
> block. I have a SQL table, populated with 14,000 records. I have
> several int columns which I need to change the default value to 0
> (zero), and a datetime column which I would like to default to todays
> date and current time. I have gone into design view for each of the
> int columns and I have simply entered 0 in the "Default value" box.
> For the datetime column, I have changed the Default Value to
> getdate().
> I have gone to enter a new record in the table, and none of the
> defaults work. All still default to <null>.
> Any ideas why this is not working? Does it only work on a table
> without existing data?
> Thanks
> Colin
>|||On Mar 21, 1:39 pm, "Bobby" <bob...@.blueyonder.co.uk> wrote:
> Hello all,
> I thought that I knew how to do this, but I must be having a mental
> block. I have a SQL table, populated with 14,000 records. I have
> several int columns which I need to change the default value to 0
> (zero), and a datetime column which I would like to default to todays
> date and current time. I have gone into design view for each of the
> int columns and I have simply entered 0 in the "Default value" box.
> For the datetime column, I have changed the Default Value to
> getdate().
> I have gone to enter a new record in the table, and none of the
> defaults work. All still default to <null>.
> Any ideas why this is not working? Does it only work on a table
> without existing data?
> Thanks
> Colin
Existing data values in columns will not change when you introduce
default values. Only new records (insertions) will have default
values .
If you want to change for the existing records , you need to update
the value.
After updating the value , alter table not to allow nulls on these
columns|||Hi
>> I have gone to enter a new record in the table, and none of the
>> defaults work. All still default to <null>.
He said that he inserted a new row as I understood it.
"M A Srinivas" <masri999@.gmail.com> wrote in message
news:1174467529.301295.252230@.l77g2000hsb.googlegroups.com...
> On Mar 21, 1:39 pm, "Bobby" <bob...@.blueyonder.co.uk> wrote:
>> Hello all,
>> I thought that I knew how to do this, but I must be having a mental
>> block. I have a SQL table, populated with 14,000 records. I have
>> several int columns which I need to change the default value to 0
>> (zero), and a datetime column which I would like to default to todays
>> date and current time. I have gone into design view for each of the
>> int columns and I have simply entered 0 in the "Default value" box.
>> For the datetime column, I have changed the Default Value to
>> getdate().
>> I have gone to enter a new record in the table, and none of the
>> defaults work. All still default to <null>.
>> Any ideas why this is not working? Does it only work on a table
>> without existing data?
>> Thanks
>> Colin
> Existing data values in columns will not change when you introduce
> default values. Only new records (insertions) will have default
> values .
> If you want to change for the existing records , you need to update
> the value.
> After updating the value , alter table not to allow nulls on these
> columns
>|||On 21 Mar, 09:06, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
> >> I have gone to enter a new record in the table, and none of the
> >> defaults work. All still default to <null>.
> He said that he inserted a new row as I understood it.
>
That's correct. I didn't mean that I wanted to change existing values,
what I meant was will default values only work on tables without
existing data.
However, I know now that they will work on tables with existing data.
I guess I've been working with Access too long. In Access, if I set
default values and then go to insert a new record directly into the
table, I can see the default values appear in the new row as I type.
However, if I do the same in SQL server, a load of nulls appear in the
new row, where the defaults should be. The defaults only appear after
I have closed the table and gone back into it. That's what confused
me.
Thanks for your help
Colin|||Boddy
See my first reply to you. After setting DEFAULT constraint have save the
table's setting ? Can you show us step by step what you did so far?
"Bobby" <bobby2@.blueyonder.co.uk> wrote in message
news:1174470005.140442.271260@.e65g2000hsc.googlegroups.com...
> On 21 Mar, 09:06, "Uri Dimant" <u...@.iscar.co.il> wrote:
>> Hi
>> >> I have gone to enter a new record in the table, and none of the
>> >> defaults work. All still default to <null>.
>> He said that he inserted a new row as I understood it.
> That's correct. I didn't mean that I wanted to change existing values,
> what I meant was will default values only work on tables without
> existing data.
> However, I know now that they will work on tables with existing data.
> I guess I've been working with Access too long. In Access, if I set
> default values and then go to insert a new record directly into the
> table, I can see the default values appear in the new row as I type.
> However, if I do the same in SQL server, a load of nulls appear in the
> new row, where the defaults should be. The defaults only appear after
> I have closed the table and gone back into it. That's what confused
> me.
> Thanks for your help
> Colin
>|||Remember also that defaults are only assigned to columns that are NOT
part of the INSERT. If you do not use a column list you will not get
a default, even if you assign NULL.
INSERT TableName VALUES('abc', NULL)
That will only work with a two column table, and will not use defaults
for either column.
INSERT TableName (col1, col10) VALUES ('abc', NULL)
That will assign the specified values to those two columns, but assign
the default - or NULL if there is no default - to all other columns.
Roy Harvey
Beacon Falls, CT
On 21 Mar 2007 01:39:08 -0700, "Bobby" <bobby2@.blueyonder.co.uk>
wrote:
>Hello all,
>I thought that I knew how to do this, but I must be having a mental
>block. I have a SQL table, populated with 14,000 records. I have
>several int columns which I need to change the default value to 0
>(zero), and a datetime column which I would like to default to todays
>date and current time. I have gone into design view for each of the
>int columns and I have simply entered 0 in the "Default value" box.
>For the datetime column, I have changed the Default Value to
>getdate().
>I have gone to enter a new record in the table, and none of the
>defaults work. All still default to <null>.
>Any ideas why this is not working? Does it only work on a table
>without existing data?
>Thanks
>Colin