Solved

How to do Add, delete and update using SQL View

Posted on 2012-03-15
6
304 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher 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

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.
Delivering innovative fully-managed cloud services for mission-critical applications requires expertise in multiple areas plus vision and commitment. Meet a few of the people behind the quality services of Concerto.

932 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

11 Experts available now in Live!

Get 1:1 Help Now