Reset a record to default values

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Reset a record to default values

Author

Topic

WindChaser
Posting Yak Master

225 Posts

Posted - 2004-07-07 : 21:15:18

Hi folks,

I'd just like to know if there is a T-SQL operation that can be used to simply reset a field to its default values.

Thanks!

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-07-07 : 21:51:21

[code]create table t ( id int identity(0,1), a int default 0, b int default 0)

insert into t
select 1,1

select * from t

update t
set
a=default,
b=default
where id = 0

select * from t

drop table t[/code]

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-07-07 : 22:04:13

Another way:

UPDATE Table
SET field1 = (SELECT Convert(int, SubString(Column_Default, 2, Len(Column_Default)-2))
FROM INFORMATION_SCHEMA.COLUMNS WHERE Table_Name='table' AND Column_Name='field1')

Bit long-winded, but it will do the job.
My example is for an integer, but the concept could be applied to any field type.
btw - the SubString(...) nonsense is required because the default values are stored with leading and trailing parentheses.

HTH,

Tim

WindChaser
Posting Yak Master

225 Posts

Posted - 2004-07-07 : 22:10:40

Thanks gents.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources