MS SQL Basics Tutorial: Managing Data through views

A view has a similar structure to a table, and when data is inserted or updated into a view, the data is actually inserted and updated by the table that is referenced by the image. However, there are some limitations to inserting, updating data and tables through a view, with specific examples of the limitations of inserting, updating, and using views.

Using the SELECT statement, you can find the record in a view and in a table. However, if you execute the following statement, you can still execute it successfully, but you can only find the data in a table instead of a view.

Note: Since inserting data into a view is essentially inserting data into the base table that it refers to, you must confirm that those columns that are included in the View column, but belong to the table, allow null values or have default values.

To execute an INSERT statement, you can only perform operations on columns that belong to the same table in the same statement. So, if you insert a row of data into the view au_title, you can only execute the following statement separately:

Insert into Au_title (author_au_id, au_lname, au_fname, contract)

VALUES (' 234-34-4611 ', ' John ', ' Smith ', 1)

Insert into Au_title (title_au_id, title_id, Au_ord, Royaltyper)

VALUES (' 234-34-4611 ', ' BU1111 ', 1,50)

There are two issues to be aware of when updating and deleting data through a view:

When the update delete is executed, the data that is deleted and updated must be included in the view result set;

If the view references more than one table, you cannot delete the data with the Delete command, and if you use Update, the updated column must belong to the same table as the insert operation.

See the full set of "MS SQL Basics Tutorials"