How to add a column and fill with a default value

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 add a column and fill with a default value

Author

Topic

richardlaw
Yak Posting Veteran

68 Posts

Posted - 2009-01-02 : 16:02:14

Hi

I'd like to know if when creating a view, I can add a column and populate each row with a default value? For example, add madeUpColumn and for evey row, use the value 'bob'.

Does this make sense?

Thanks
Richard

Richard Law

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-02 : 16:14:54

CREATE VIEW SomeView
AS
SELECT Column1, Column2, 'bob' AS madeUpColumn
FROM Table1
GO

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2009-01-02 : 16:34:51

i just have to ask: why in the world do you need that?

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.1 out!

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-01-02 : 16:37:52

quote:
Originally posted by spirit1

i just have to ask: why in the world do you need that?

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.1 out!

I can think of a scenario where a default value of 0 would be better than NULL.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-02 : 16:40:54

quote:
Originally posted by Skorch

quote:
Originally posted by spirit1

i just have to ask: why in the world do you need that?

I can think of a scenario where a default value of 0 would be better than NULL.

That doesn't seem to related to this thread. Replacing a NULL with 0 would be done with COALESCE/ISNULL or from within the application. Adding a bogus column seems much different.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2009-01-02 : 16:41:42

but still... what's the point? you can't update the column....
if you meant a default value in an existing column then you'll have to use the ISNULL function.

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.1 out!

richardlaw
Yak Posting Veteran

68 Posts

Posted - 2009-01-02 : 16:56:01

The reasons is, the SQL output is sent to a web page which has the option to save the table data to an Excel file - from there the user coppies the date and pastes it into another form. My web page offers most of the columns, but there are two columns that I can't/don't need to provide which in my case will always have the same value for every row regardless. I hope this makes sense! - I just wanted a way to give someone a Excell file they could copy and paste real data into an empty form with all columns taken care of.

Thanks

revdnrdy
Posting Yak Master

220 Posts

Posted - 2009-01-02 : 17:24:17

quote:
Originally posted by richardlaw

The reasons is, the SQL output is sent to a web page which has the option to save the table data to an Excel file - from there the user coppies the date and pastes it into another form. My web page offers most of the columns, but there are two columns that I can't/don't need to provide which in my case will always have the same value for every row regardless. I hope this makes sense! - I just wanted a way to give someone a Excell file they could copy and paste real data into an empty form with all columns taken care of.

If this is the case why not just create the columns & let them be filled with null? Why provide any default at all if it is a don't care situation? Seems like extra work to me..

r&r

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-02 : 17:29:01

Another approach would be not to do it in the view, but rather in the query that is calling the view. This way you don't have to modify the view plus you wouldn't break any code that was potentially doing a SELECT * against the view.

SELECT Column1, Column2, 'bob' AS madeUpColumn
FROM SomeView

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

richardlaw
Yak Posting Veteran

68 Posts

Posted - 2009-01-02 : 17:48:10

Great ideas - thanks all. I've not done a lot with SQL, but the more I get into .NET, the more I believe all the data manipulation needs to be managed in SQL, not in .NET.

Thanks again.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-01-05 : 03:20:58

quote:
Originally posted by spirit1

i just have to ask: why in the world do you need that?

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.1 out!

The only case I have seen is when you design reports(like the one such as SAS,etc) which would accept
only table/view as datasource, you should create a view to populate extra columns

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources