Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How to do Add, delete and update using SQL View

Posted on 2012-03-15
6
Medium Priority
?
330 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 184 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 368 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 184 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 184 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 368 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

824 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