alter view query - SQL Server Forums

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)

alter view query

Author

Topic

agarwasa2008
Posting Yak Master

109 Posts

Posted - 2009-09-11 : 17:17:58

I wish to do 2 things:

1. Alter a VIEW. I have written the sql query below that I have used.
2. use a select statment to just populate this new column

alter view dbo.v_productInfo
add pName nvarchar NULL

Error Message:
Msg 156, Level 15, State 1, Procedure v_productInfo, Line 2
Incorrect syntax near the keyword 'add'.
Msg 102, Level 15, State 1, Procedure v_productInfo, Line 2
Incorrect syntax near 'pName'.

SA

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-09-11 : 17:46:37

You cannot "add" a column to a view as you would a table. You need to have the entire view definition scripted, add the new column to it, and run the ALTER VIEW command with the updated definition:

CREATE VIEW myView AS SELECT a,b,c FROM myTable
GO
ALTER VIEW myView AS SELECT a,b,c,d FROM myTable
GO

agarwasa2008
Posting Yak Master

109 Posts

Posted - 2009-09-11 : 17:56:39

Thank you it works like a charm!!

SA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources