Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>SQL Server views can be updated just as if they were a single table, as long as they conform to certain conditions.</p> <p>From the <a href="http://msdn.microsoft.com/en-us/library/ms187956.aspx" rel="nofollow">documentation</a>:</p> <blockquote> <p>Updatable Views</p> <p>You can modify the data of an underlying base table through a view, as long as the following conditions are true:</p> <p>Any modifications, including UPDATE, INSERT, and DELETE statements, must reference columns from only one base table.</p> <p>The columns being modified in the view must directly reference the underlying data in the table columns. The columns cannot be derived in any other way, such as through the following:</p> <p>An aggregate function: AVG, COUNT, SUM, MIN, MAX, GROUPING, STDEV, STDEVP, VAR, and VARP.</p> <p>A computation. The column cannot be computed from an expression that uses other columns. Columns that are formed by using the set operators UNION, UNION ALL, CROSSJOIN, EXCEPT, and INTERSECT amount to a computation and are also not updatable.</p> <p>The columns being modified are not affected by GROUP BY, HAVING, or DISTINCT clauses.</p> <p>TOP is not used anywhere in the select_statement of the view together with the WITH CHECK OPTION clause.</p> <p>The previous restrictions apply to any subqueries in the FROM clause of the view, just as they apply to the view itself. Generally, the Database Engine must be able to unambiguously trace modifications from the view definition to one base table. For more information, see Modify Data Through a View.</p> </blockquote> <p>I don't believe SSMS is doing anything special - editing the contents of a view offers exactly the same functionality as editing the contents of a table. If the user attempts to make a change that does not conform to the above conditions, SSMS will likely display an error.</p> <blockquote> <p>How does SSMS infer which primary key to use, even if the key is not in the view?</p> </blockquote> <p>It doesn't. SQL Server does since only one underlying table can be edited at a time.</p> <blockquote> <p>How does SSMS determine which column inside a view can or can not be edited / inserted / deleted etc.?</p> </blockquote> <p>Again, it's SQL Server that determines this, not SSMS.</p> <blockquote> <p>What would be my best option to replicate this inside a .NET application?</p> </blockquote> <p>As long as all your views conform to the above conditions, simply do the same as you're doing for tables, but be ready to handle the errors from users doing something they can't (this implies some user training will be required, just as it would be if they were using SSMS directly).</p>
    singulars
    1. This table or related slice is empty.
    1. PO.NET Updating Data in SQL View
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USDaniel Renshaw
    1. USDaniel Renshaw
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COIn addition, in case the view is NOT updatable, you can STIL Lmake it so by providing an instead of update trigger ;) Which then can do the real table inserts.
      singulars
      1. PO
      1. USTomTom
    2. COThanks Daniel, that's ok if I can indeed get SQL to do all the work for me (which is preferable) but with normal .NET I don't have a constant connection to SQL so I need some form of a WHERE clause to pass along with the update statement to update the correct row in the view. Would selecting data along with the ROW_NUMBER function work, or I could try using a DataGridView in virtual mode with a ADODB RecordSet which can do direct edits to the database.
      singulars
      1. PO
      1. USMarlon
    3. COYou should use whatever single column, or combination of columns, identifies the rows in the view. If there is no combination of columns able to do this job (i.e. it's feasible that the view may contain rows with identical values in every column) then you'll need to add a new column to the view which can be used as an identity. As long as all the underlying tables have primary keys, the view will also have a primary key composed of the combination of all the underlying primary key columns, though all those columns must be exposed by the view, and things change if the view has some aggregation.
      singulars
      1. PO
      1. USDaniel Renshaw
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload