Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Adding a column to view Expand / Collapse
Author
Message
Posted Monday, December 7, 2009 9:30 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, September 26, 2010 8:29 AM
Points: 113, Visits: 265
Is it able to add a column to a view or should we drop the existing view and create new view?
Post #830002
Posted Monday, December 7, 2009 10:03 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, May 15, 2014 5:11 PM
Points: 6,067, Visits: 5,283
I belive in SQL 2000 you could use the ALTER VIEW command..

CEWII
Post #830044
Posted Monday, December 7, 2009 10:48 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, September 26, 2010 8:29 AM
Points: 113, Visits: 265
Thanks for the reply.

But is there a command like ALTER VIEW view_name ADD column_name datatype
Post #830075
Posted Monday, December 7, 2009 11:23 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:05 AM
Points: 42,829, Visits: 35,961
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 2008, MVP
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

Post #830107
Posted Monday, December 7, 2009 12:20 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, September 26, 2010 8:29 AM
Points: 113, 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?
Post #830170
Posted Monday, December 7, 2009 12:34 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, May 15, 2014 5:11 PM
Points: 6,067, Visits: 5,283
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
Post #830188
Posted Monday, December 7, 2009 1:13 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, September 26, 2010 8:29 AM
Points: 113, Visits: 265
thank you
Post #830222
Posted Tuesday, December 8, 2009 10:42 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, September 26, 2010 8:29 AM
Points: 113, 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
Post #830832
Posted Tuesday, December 8, 2009 10:54 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:05 AM
Points: 42,829, Visits: 35,961
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 2008, MVP
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

Post #830837
Posted Tuesday, December 8, 2009 11:29 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, September 26, 2010 8:29 AM
Points: 113, 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

Post #830862
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse