0

i am studying about views in sql and somewhat confused and having the following query :

i have a table 'item' and need to insert data into it. am using two ways, one without view and the other using a view.

CREATE VIEW vw_item
SELECT * FROM item

  1. Inserting via the view:

    INSERT INTO vw_item values(...)

  2. Inserting via the table:

    INSERT INTO item values(...)

What's the difference in query 1 and 2? That is, directly inserting into the table & using a view to insert into the table.

Is there a difference in insertion only when we use CHECK OPTION while creating the view?

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
sqlchild
  • 8,754
  • 28
  • 105
  • 167

3 Answers3

3

There is no difference between the two INSERT statements. Views can be updateable, but there are restrictions on what makes views updateable.

The CHECK OPTION prevents changes that do not meet the view's criteria. If your view example had a WHERE clause, the column(s) involved could not be updated -- but other columns in the table could be.

Reference

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
  • @OMG Ponies : thanks a lot ma'am for your great answer, also i would like to know that how do views act as a mediator between the actual tables and an end user – sqlchild Mar 10 '11 at 05:27
  • @OMG Ponies : http://stackoverflow.com/questions/5255891/use-of-views-to-protect-the-actual-tables-in-sql – sqlchild Mar 10 '11 at 05:56
  • @OMG Ponies :what's the main purpose of views? – sqlchild Mar 10 '11 at 05:58
  • @OMG Ponies : how do views abstract the actual tables from the end users? they just expose the alias name instead the actual name of the tables. is this the only way or more are there? – sqlchild Mar 10 '11 at 06:06
  • @sqlchild: Abstraction/encapsulation occurs because you use a view reference -- if you don't have privilege to view the CREATE VIEW statement, you don't know what table(s) it is operating on or any other details (IE: ORDER BY, which is bad in a view for this very reason) – OMG Ponies Mar 10 '11 at 06:11
  • @OMG Ponies : ma'am , actually i want that the end users first insert into a mediator and then the data goes from the mediator to the actual tables because i want to protect my actual tables from getting spoiled up by some wrong users or power outage. so what should i use here? – sqlchild Mar 10 '11 at 06:14
  • @OMG Ponies : ok, so you mean to say that, as the end users don't know the ACTUAL NAME of the tables, but they know the name of the VIEWS instead. this way the abstraction happens. – sqlchild Mar 10 '11 at 06:17
  • @sqlchild: Use a stored procedure, but you need a UPS/etc to protect against power outages. And yes, you understand how the abstraction/encapsulation works with views now. – OMG Ponies Mar 10 '11 at 06:17
  • @OMG Ponies : but using a stored procedure, if i want to insert only a how can i restrict what a users INSERTS using a insert query, which could be restricted using WITH CHECK OPTION of a view. – sqlchild Mar 10 '11 at 06:20
  • @sqlchild: Posting a new question to ask that – OMG Ponies Mar 10 '11 at 06:22
  • @OMG Ponies : ok, are you posting or shall i ? please give me the url too. – sqlchild Mar 10 '11 at 06:27
  • @OMG Ponies : http://stackoverflow.com/questions/5256238/inserting-and-viewing-data-simultaneously-from-a-single-table – sqlchild Mar 10 '11 at 06:28
2

specifics on MS-SQL:

  1. OMG Ponies' answer has the bulk of what you're looking for, start there.

  2. Regarding how a VIEW works, according to How much space does a view take up in Microsoft SQL Server, the view only "materializes" (i.e. to retrieve data) when it is called, unless it is an Indexed View.

  3. Having an indexed view adds additional overhead with inserts/updates/deletes. Source: http://www.novicksoftware.com/Articles/Indexed-Views-Basics-in-SQL-Server-Page-4.htm

  4. Misc. info on optimizing indexed views: http://technet.microsoft.com/en-us/library/cc917715.aspx

Some specifics on MySQL (maybe useful to others curious about VIEWs): http://dev.mysql.com/doc/refman/5.0/en/view-updatability.html

Other database servers probably have slight differences with performance/indexing/updatablility, so keep that in mind for your particular database.

Community
  • 1
  • 1
Dolan Antenucci
  • 15,432
  • 17
  • 74
  • 100
  • 2
    You've got enough rep to know about tags -- this is for SQL Server, not MySQL. The information is similar, but not guaranteed to always be the case. – OMG Ponies Mar 10 '11 at 05:08
  • @OMG Ponies: Damn, I would hope think so too, but still a rookie here apparently. I'll update my post accordingly. Thanks! – Dolan Antenucci Mar 10 '11 at 05:18
  • @OMG Ponies: what's proper etiquette with a post like mine? Should I leave the MySQL info since it isn't exactly on topic? – Dolan Antenucci Mar 10 '11 at 05:29
  • I wouldn't include the MySQL content, or at least put it at the bottom of the answer so people don't see the MySQL content & kneejerk downvote for it. – OMG Ponies Mar 10 '11 at 05:42
0
CReate table param1(id int,num int);
CReate table param2(id int,num int);
INSERT INTO param1 VALUES(1,1),(2,2),(3,3);
INSERT INTO param2 VALUES(1,1),(4,4),(3,3); 


CREATE VIEW vie(id) AS 
SELECT Id FROM param1 WHERE num IN (SELECT num FROM param2); 

INSERT INTO  vie VALUES(100); 
SELECT * FROM vie;
SELECt * FROM param1;
zloctb
  • 10,592
  • 8
  • 70
  • 89