[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 337
  • Last Modified:

How to do Add, delete and update using SQL View

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
ziorinfo
Asked:
ziorinfo
5 Solutions
 
kevb258Commented:
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
 
jogosCommented:
'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
 
Anuradha GoliSystems Development / Support SpecialistCommented:
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
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

 
Scott PletcherSenior DBACommented:
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
 
ziorinfoAuthor Commented:
if the view is made by using multiple table then can I do these operation on it
0
 
jogosCommented:
<<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

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now