How to insert new data in a SQL View table

Posted on 2014-08-04
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
    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

    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

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    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.
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.

    737 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

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now