How to default SQL column 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 2005 Forums

Transact-SQL (2005)

How to default SQL column values

Author

Topic

ryanlcs
Yak Posting Veteran

62 Posts

Posted - 2010-01-22 : 01:53:05

How can I default a SQL column's values with values from another column?

Eg: Column having data

Column1 Column2
1 Parameter1
2 Parameter2
3 Parameter3

The column1 is index running number. The column2, I wanted the default value to be 'Parameter', added the value from column1 into column2, which make up 'Parameter1', 'Parameter2' and etc.

How can I do that.

Thank You.

nathans
Aged Yak Warrior

938 Posts

Posted - 2010-01-22 : 02:24:41

Are you going to store this data, or do you just want it in a query result? I dont see the point in storing it, so Ill assume you want the select statement:

For example:

select Column1, 'Parameter' + cast(Column1 as varchar(10)) [Column2]
from dbo.YourTable

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-22 : 02:33:04

You can do it using a computed column:

create table yak(id int identity(1,1), col2 as 'Parameter'+convert(varchar(10),id),col3 varchar(255))
insert yak(col3)
select 'first test' union all
select 'second test'

select * from yak

drop table yak

No, you're never too old to Yak'n'Roll if you're too young to die.

ryanlcs
Yak Posting Veteran

62 Posts

Posted - 2010-01-22 : 03:37:32

Hi

Actually i wanted set default value at design time, so when the user add new record, the "Parameter1","Paramater2" will be inserted automatically.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-22 : 03:52:12

Yes - so what is wrong with my solution?

No, you're never too old to Yak'n'Roll if you're too young to die.

Kristen
Test

22859 Posts

Posted - 2010-01-22 : 03:54:51

Can the user supply a value when creating a new record? or only "override the default" when modifying the record later?

Actaully, for either of those scnearios I think I would use an AFTER INSERT trigger to replace any NULL value with


'Parameter'+convert(varchar(10),id),col3 varchar(255))

but that will entail setting the column to allow nulls (bit daft if the column is immedaitely defaulted) or using a BEFORE TRIGGER (which, IME, are a PITA

)

Personally I would leave the column NULL, to imply the default value, and then allow the user to enter a value when they wish to.

My real problem with this, I guess, is that the user might be happy with "Parmameter1" as the default value, or they might enter a different value. If I leave the column NULL I *know* if the user actually chose a value, but if I put in "Parameter1" I don't know if the user has seen that value, and agreed with it, or just not bothered to put anything in.

But your circumstances may be entirely different, of course

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources