Learn how to a build a cloud-first strategyRegister Now


How to insert new data in a SQL View table

Posted on 2014-08-04
Medium Priority
Last Modified: 2014-08-13
I need to know how to update a SQL View in SQL Server Management Studio 2012. When I run the insert statement another table is update and not the view.

1.) Can I insersert data into  a SQL View
        - The view is writeable
2.) If I can insert data into a SQL View  how do I do this?
3.) Why is the view not inserting the new data and the other table is inserted the new data. Which causes duplicate rows?
4.) If I can not insert new data into a view how does the view get new data inserted into it?

My insert statement is a typical statement.
      INSERT INTO  dbo.view_Table (Column1, Column2, Column3, etc.....) VALUES ('data1', 'data2', data3', etc.....)
Thank you,
Question by:newjeep19
LVL 28

Expert Comment

by:Ryan McCauley
ID: 40239636
If your view is just based on a single table and includes the primary key, you can transparently insert data into the view and SQL Server will do the necessary insert into the underlying table (assuming you don't violate NOT NULL or other constraints, etc).

However, if you've got a more complicated view that involves more than one table, you can't insert data into it directly. However, SQL Server allows you to attempt to insert data into a view - you can set up an "INSTEAD OF" trigger on the view that allows the data you've attempted to insert to be redirected to another table that does support inserts. I don't see it used commonly (as it generally leads to the sort of confusion you're seeing now), but it's possible that way.

Check to see if there are any triggers on your view, and the code behind them will tell you what's going on.

Author Comment

ID: 40241986
Thank you for your post. However, I am still not able to update the view. Only the table. There is no primary key for the view. There are no constraints that I am aware of. However, I did not create the view and the person that did is no longer at my company. So, there was no knowledge exchange. I am not sure how to set up a "INSTEAD OF" trigger. Please help.
LVL 75

Accepted Solution

Anthony Perkins earned 2000 total points
ID: 40242737
However, I am still not able to update the view. Only the table. There is no primary key for the view.
The question you have to ask yourself is as follows:  "Is the VIEW updatable?"  If the answer is yes, then it should not be a problem.  if you are not sure, here are the conditions as stated in SQL Server BOL:
Any modifications, including UPDATE, INSERT, and DELETE statements, must reference columns from only one base table.
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:
          1.  An aggregate function: AVG, COUNT, SUM, MIN, MAX, GROUPING, STDEV, STDEVP, VAR, and VARP.
           2. 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.
The columns being modified are not affected by GROUP BY, HAVING, or DISTINCT clauses.
TOP is not used anywhere in the select_statement of the view together with the WITH CHECK OPTION clause.

If you are still not sure post the schema for the table and the VIEW and we can tell you.

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question