SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Adding a column to view


Adding a column to view

Author
Message
rahulsony111
rahulsony111
SSC Veteran
SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)

Group: General Forum Members
Points: 285 Visits: 265
Is it able to add a column to a view or should we drop the existing view and create new view?
Elliott Whitlow
Elliott Whitlow
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10004 Visits: 5314
I belive in SQL 2000 you could use the ALTER VIEW command..

CEWII
rahulsony111
rahulsony111
SSC Veteran
SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)

Group: General Forum Members
Points: 285 Visits: 265
Thanks for the reply.

But is there a command like ALTER VIEW view_name ADD column_name datatype
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86328 Visits: 45232
No.
ALTER VIEW <ViewName> AS
<Select Statement>



How would add column work? You're saying nothing about which table that column comes from. A view's just a saved select statement, it's got no actual storage like a table does.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


rahulsony111
rahulsony111
SSC Veteran
SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)

Group: General Forum Members
Points: 285 Visits: 265
So i have to alter table first
like ALTER TABLE table_name ADD column_name datatype

Later should i drop the view and create a new view to add that additional column?
Elliott Whitlow
Elliott Whitlow
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10004 Visits: 5314
ALTER VIEW is kind of like a drop and rebuild in that you have to specify the whole SELECT statement that makes up the view. But you can also do a DROP/CREATE, the downside to that is that granted permissions are also dropped.

CEWII
rahulsony111
rahulsony111
SSC Veteran
SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)

Group: General Forum Members
Points: 285 Visits: 265
thank you
rahulsony111
rahulsony111
SSC Veteran
SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)

Group: General Forum Members
Points: 285 Visits: 265
Please check this syntaxes(NOT ONLY SYTAXES BUT A CODE TO RUN FAST)

Is this syntax correct
I want to add column to a table and set default value for that column to be 'N'
ALTER TABLE DWT00050_IMC_CCYYMM_FACT
ADD COLUMN CONTRB_DISTB_FLG char(1) {SET DEFAULT 'N'}

After altering table i have to alter view. I have to add new column to view
Please check the syntax
ALTER VIEW view_name
("column 1", "col 2", "col3", "col4")
SELECT ("col5", "col6", "col7", "col8")
FROM table_name
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86328 Visits: 45232
rahulsony111 (12/8/2009)
ALTER TABLE DWT00050_IMC_CCYYMM_FACT
ADD COLUMN CONTRB_DISTB_FLG char(1) {SET DEFAULT 'N'}


Nope. Invalid syntax there. Check Books Online for the syntax of an ALTER TABLE ADD ... statement

ALTER VIEW view_name
("column 1", "col 2", "col3", "col4")
SELECT ("col5", "col6", "col7", "col8")
FROM table_name


Completely incorrect, that'll never run. The select statement that you've given there isn't even correct.
I suggest you look in Books online both for the basic syntax of a SELECT statement and for the syntax for CREATE/ALTER VIEW.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


rahulsony111
rahulsony111
SSC Veteran
SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)

Group: General Forum Members
Points: 285 Visits: 265
How about this code

ALTER TABLE DWT00050_IMC_CCYYMM_FACT
ADD CONTRB_DISTB_FLG char(1) [DEFAULT 0]

ALTER VIEW view_name
AS
SELECT ("col1", "col2", "col3", "col4")
FROM table_name
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search