How to insert new data in a SQL View table

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,
newjeep19Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan McCauleyData and Analytics ManagerCommented:
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.
0
newjeep19Author Commented:
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.
0
Anthony PerkinsCommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.