Solved

How to do Add, delete and update using SQL View

Posted on 2012-03-15
6
308 Views
Last Modified: 2012-04-02
I need to know that if I have sql view then can I do add, delete and update operation on it using SQL view.

If yes then how can I do this
0
Comment
Question by:ziorinfo
6 Comments
 
LVL 4

Assisted Solution

by:kevb258
kevb258 earned 46 total points
ID: 37724150
It should be possible.
I did this test:
1) CREATE VIEW VIEW1 AS SELECT * FROM TABLE1
2) SELECT * FROM VIEW1 (to check it was ok)
3) UPDATE VIEW1 SET COL2='ABC' where COL1=123

and this worked fine.
0
 
LVL 25

Assisted Solution

by:jogos
jogos earned 92 total points
ID: 37724329
'SQL View' as in a product or as in a database object (create view..)

When speaking of the object

See info at 'updatabble views' on following link
http://msdn.microsoft.com/en-us/library/ms187956.aspx
or a dedicated topic at
http://msdn.microsoft.com/en-us/library/ms180800.aspx
0
 
LVL 12

Assisted Solution

by:Anuradha Goli
Anuradha Goli earned 46 total points
ID: 37724339
You can perform add / delete operations on View

Craete view :
 CREATE VIEW VIEWNAME1  AS SELECT * FROM TABLE1

To insert a row into View:
 INSERT INTO VIEWNAME1 (COL1) values('TESTDATA')

To retrieve/View the data from View:
SELECT * FROM VIEWNAME1

To delete row from View:
DELETE FROM VIEWNAME1 where COL1=<colvalue>

To update the data from View:
 UPDATE VIEW1 SET COL1='NEWDATA' where COL2=<colvalue>
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 46 total points
ID: 37725395
It depends on the view definition.  SQL has to be able to trace the view column back unambiguously to a specific column in a base table.

If the view contains all the required key columns, and does not use aggregation, then typically you can use the view for modifications.

For INSERTs, the view must also contain every non-nullable column that does not have a default value specified.

Certain other restrictions apply.  For example:

http://msdn.microsoft.com/en-us/library/ms180800.aspx
0
 

Author Comment

by:ziorinfo
ID: 37728061
if the view is made by using multiple table then can I do these operation on it
0
 
LVL 25

Accepted Solution

by:
jogos earned 92 total points
ID: 37728297
<<if the view is made by using multiple table then can I do these operation on it>>
Limitations are in the documentation on the links I posted
#a37724329.

Repeating that
See info at chapter 'updatabble views' on following link
http://msdn.microsoft.com/en-us/library/ms187956.aspx
or a dedicated topic at
http://msdn.microsoft.com/en-us/library/ms180800.aspx



One that certainly apply to your multitable view
Any modifications, including UPDATE, INSERT, and DELETE statements, must reference columns from only one base table.

And if your stuck with limitations
If the restrictions described previously in this topic prevent you from modifying data directly through a view, consider the following options:

Use INSTEAD OF triggers with logic to support INSERT, UPDATE and DELETE statements. For more information, see Designing INSTEAD OF Triggers.

Link to learn more of that http://msdn.microsoft.com/en-us/library/ms175089.aspx


But think about why you are doing this. A multi-table view is easy to get all things with one query, a difficult query that you only have to (let) write once.  The danger of starting to update through a view is that you start using the heavy multi-join view to access and update only the data of a single base-table.  Not so peformant.
And today you want to update a name and a date (from tableA) , that's fine. Next week you yust add the update of a 3th column and it does not work. Why?? Oh, error message says something weard about non updatable view ....
Same can occur when some change on the view makes it not updatable anymore.

My suggestion:
Try to make stored procedures to insert, update and delete instead of doing it through the view. http://social.msdn.microsoft.com/search/en-us?query=create+procedure&x=0&y=0
Instead of trigger is same sollution , but neads more skill to comprehend.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

821 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