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.
| Author |
Topic |
|
bscivolette
Starting Member
9 Posts |
 Posted - 2012-09-18 : 11:48:45
|
| Good Afternoon,I am not a SQL savvy person so I apologize for the simplicity of this question.I am trying to update a system view on SQL 2008 by adding two new columns from existing tables. The view itself is used to generate a specific e-mail and the new columns will allow me to include information that is missing. Based on my research I have been unable to confidently determine how to update/add a column without changing any other aspect of the current view. I was hoping someone here could give me some insight so that the view, aside from my additions, will remain in tact. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-09-18 : 12:57:09
|
| you cant update system views!if its user view it will not updatable unless it follows below conditionshttp://msdn.microsoft.com/en-us/library/ms187956(v=sql.90).aspxworkaround is to use INSTEAD OF trigger to make it updatable in most cases where it cant be done------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
bscivolette
Starting Member
9 Posts |
Posted - 2012-09-18 : 14:55:10
|
| Good Afternoon,Thank you for the reply. I was able to find a possible work around by right clicking on the view and then: ‘Script View As’ > ‘Alter To’ > ‘New Query Editor Window’. This brings up an editor with 'ALTER VIEW AS' followed by a full list of all associated tables listed under 'SELECT' and 'FROM'. Luckily for me it appears that the tables I was trying to add are already referenced in the SELECT portion, I believe I now just need to JOIN them in the FROM section. The problem is that I have no clue how to use the JOIN query correctly. Assuming I have provided enough information I would appreciate any input on how to create a JOIN query. The table is dbo.LocationAccessType and the column I am referencing in that table is LocationAccessTypeDescription. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-09-18 : 15:07:11
|
| so whole question was not on updating view after all but it was rather on 'altering' the view. Its completely different to what you asked for in the first place!Anyways here's link to understand on how the JOIN workshttp://www.w3schools.com/sql/sql_join.asp------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
bscivolette
Starting Member
9 Posts |
Posted - 2012-09-19 : 10:21:23
|
| Good Morning,Sorry for the confusion. I should have mentioned that I don't know too much about SQL, I'll be sure to carefully pick my words next time. Thanks for your help. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-09-19 : 11:42:02
|
no problemyou're welcome  ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
Updating a View in SQL