Updatable views in sql server Part 40
HTML-код
- Опубликовано: 11 сен 2024
- In this video we will learn about updating a view that is based on one or more base tables.
Text version of the video
csharp-video-tu...
Healthy diet is very important both for the body and mind. If you like Aarvi Kitchen recipes, please support by sharing, subscribing and liking our RUclips channel. Hope you can help.
/ @aarvikitchen5572
Slides
csharp-video-tu...
All SQL Server Text Articles
csharp-video-tu...
All SQL Server Slides
csharp-video-tu...
All Dot Net and SQL Server Tutorials in English
www.youtube.co...
All Dot Net and SQL Server Tutorials in Arabic
/ kudvenkatarabic
Your presence on earth is heavenly gift from God. Thank you so much.
True 😃
@Venkat...You can't even imagine how are you helping thousands of people from your light of knowledge. The clarity in your explanation is amazing. Thank you vey much for taking time for all these efforts. God bless you son.
ह
Hi Anurag, Thank you very much for taking time to give feedback. Feedback like this is a morale booster for making more videos. Thank you again for taking time. Very few people do this.
Man, your tutorials simply the BEST. Anyone can master on SQL Server & .NET Framework with these tutorials. These are worth thousand of dollars in my opinion and thousand times better than crappy books and paid services. So grateful, thanks.
Thank you so much Venkat, I learned more from your tutorials than from a University course. I have my first interview for a SQL position next week... thanks to you man !!!
Did you got the job ?
The example of updating the base table wrongly is very carefully incorporated in the tutorial. It warns me of the possible pitfall of blindly categorizing the views into "Update possible" and "Update NOT possible". You certainly have a great flair for not just technology but also for teaching. Thank you and All the best for all you do!
this man is a god!!!!! anyone want to learn sql, this is definitly the place to come!!!
The method as you describe and explain the issues is amazing. I have spent the last few days, learned so much through your videos. Thank you @kudvenkat from Germany. I'll take a look and spread all your videos. You are a Great Teacher and a Big Personality! Thanks a lot! kudvenkat
Thanku kudvenkat. you are like a god gift for students like us
I agree with Anurag...so cool and simple...but to THE BEST. Cheers Venkat...I'm just loving your videos and teaching...completed all of C# and turned to SQL...and aiming to cover the .NET after these...
Your teaching is very impressive and injecting a special interest in me !!
Hi Venkat. I recently started watching your videos and you are amazing! You explain everything with so much clarity. I have an interview for a technical SQL position and I feel so much more prepared and confident after watching your videos. Thank you!!
I wonder why your videos are not the most viewed videos on youtube. Salute Sir!
Thank you so much for sharing your knowledge with us.This is very helpful.
really great videos.. i have gone through most of ur videos on sqlserver.. its all well explained.. Thank you so much !!
Revisiting
Thanks a lot for educating us
This is a very good tutorial, thank youu
Everything explained very nicely and also showed the pitfall.
All your training help me so much !!! thanks you
There are many problems here. What if our Id columns are identity columns? What if our Salary column in the base table is NOT NULL column etc ...
U are great teacher ! Thank U for your explanation
*Regarding Questions on:* *DML & Inserting, Deleting, Updating through a VIEW.*
A VIEW is essentially a "Virtual Table" (or a "Stored Query"). Once created a View can be treated just like a table. (A Stored Procedure can not be treated like a table.) A View doesn't store data, it defines a way to look at data which resides in underlying table(s). Row and column level security can be introduced as shown in the video, and Permissions and Security Predicates can also be added to the View to further restrict available functionality.
IMPORTANT NOTES:
--------------------------------
1. A VIEW allows access to Columns in an Underlying Table or Tables. Only those columns from the underlying Table which are "SELECTED" in the VIEW can be seen or accessed by users of the VIEW.
2. All Columns SELECTED in the VIEW can be SELECTED by users of the VIEW.
3. ALL Columns SELECTED in the VIEW _may_ be UPDATEable if they are _otherwise_ user updateable. This can be deceptive and dangerous, however, in cases where the column has come from a JOINed table. This is dangerous because it allows the update of a primary key attribute as if it applied only to the foreign key record. It obfuscates the fact that this value is one-to-many and many be related to other records also.
4. Where a VIEW SELECTS only from a single table, *UPDATEs, DELETEs, and INSERTs* can be done on the underlying Table Columns/Rows through the VIEW, assuming all constraints can be satisfied.
5. Where a VIEW SELECTS from _multiple_ Tables by using a JOIN, *UPDATEs* can be done on the underlying (multiple) Tables' Columns through the VIEW assuming all constraints can be satisfied, but *DELETEs, and INSERTs* can NOT be done on the Underlying Tables' Rows through the VIEW. Attempting to DELETE or INSERT on a VIEW with JOINed Tables will fail and produce a message like: "Msg 4405,... View or function 'vwViewName' is not updatable because the modification affects multiple base tables."
6. For a VIEW which has not SELECTed all columns in the Underlying Table, INSERTING a Record through the VIEW will create a record in the underlying Table which has NULLs in the non-SELECTED (inaccessible) columns. If the Underlying Table has constraints like "NOT NULL" on these columns, the INSERT will fail and produce a message like: "Msg 515,... Cannot insert the value NULL into column 'ColumnName', table 'DBName.dbo.tblTableName'; column does not allow nulls. INSERT fails." If the VIEW-based INSERT results in a NULL in a column defined as a DATE type, the INSERT will fail and produce a message like: "Msg 241,... Conversion failed when converting date and/or time from character string."
Best Regards.
Your videos have helped me greatly .. Thank you so much
this is very nice, sir!
Hello Sir,
Your all videos would help us and I will watch your all videos.
Thank you very much for your great teaching sir
Hello Jyotima - Thank you very much for the feedback. Means a lot. I have included all the sql tutorial videos, slides and text articles in sequence on the following page. Hope you will find it handy.
www.pragimtech.com/courses/sql-server-tutorial-for-beginners/
When you have some time, can you please leave your rating and valuable feedback on the REVIEWS tab. It really helps us. Thank you. Good luck and all the very best with everything you are doing.
You are the man, well done.
thank you so much, greetings from Mexico
hi venkat how are you today...I have question like this.. here views. got updated it is based on multiple table. but wrong values.in instead of trigger it is also 2 under laying table but why error...ie why not allowing
Great tutorial!
You are doing a really good job.
thank you for this video, it is very cool and useful.
Venkat Sir, Thank you very much for your videos. In this video of PART-40 Updatable views in SQL server at 5:05 in this video when we execute
Insert into vWEmploteeesDataExceptSalary values (2, 'Mikey', 'Male',2) the values has inserted but what about the Salary field of Mikey, we have not supplied any value to that field, So it may get NULL or any other value?
gd question
For the salary column either it should have a default value set or Allow nulls should be checked. Otherwise we will get an error while trying to update the view.
awesome! thanks a lot!
Sir please make a video of partitioning of table of sql methods like
1.create the partition function
2.create partition schema
3. Create partition table with schema
Log files
Please make this video
GREAT STUFF
you are the best sir ...
Great video!
when i insert the data from view i got this error."View or function 'vWEmployeesByDepartment' is not updatable because the modification affects multiple base tables".
u can update view if that is based on single base table for multiple base table updation instead of triggers are used
You are excellent. I love your tutorial. God bless you. I have question here I have virtual tables dbo.ETTO. Actually I am creating view on view.
CREATE VIEW vwPOheader
WITH SCHEMABINDING
AS
SELECT PO, PODATE, VDRNBR, TERM1, TERM2 FROM dbo.ETTO
ERROR MESG:
cannot schema bind with view 'vwPOheader', dbo.ETTO is not schema bound (#4513)
really helpful videos,nice
Thank you very much for taking time to give feedback. This means a lot. I am very glad you found the videos useful.
I have organised all the Dot Net & SQL Server videos in to playlists, which could be useful to you
ruclips.net/user/kudvenkatplaylists?view=1&sort=dd
If you need DVDs or to download all the videos for offline viewing please visit
www.pragimtech.com/order.aspx
Slides and Text Version of the videos can be found on my blog
csharp-video-tutorials.blogspot.com
Tips to effectively use my youtube channel.
ruclips.net/video/y780MwhY70s/видео.html
If you want to receive email alerts, when new videos are uploaded, please subscribe to my youtube channel.
ruclips.net/user/kudvenkat
If you like these videos, please click on the THUMBS UP button below the video.
May I ask you for a favor. I want these tutorials to be helpful for as many people as possible. Please share the link with your friends and family who you think would also benefit from them.
Good Luck
Venkat
In practical, is updating or delting data through views is done?Is it feasible?
Gorgeous!
hi,KudVenkat if you say views are used for security purpose so that user cannot update or delete in actual table and you showed that after updating views the data in table it uses also gets updated then where is the security in this?
Hi Sir,
Can you please help me on the below query which is while I am trying to deleting the record getting below error.
I 've joined two tables in below query now here I am trying to delete name from list but while i am trying to update getting this below error and this I am facing this issue while inserting and deleting a records from a view
ERROR
------------
Msg 4405, Level 16, State 1, Line 28
View or function 'dbo.emp_dept' is not updatable because the modification affects multiple base tables.
QUERY
-----------
SELECT A.ID,A.NAME,A.GENDER,B.DEP_NAME FROM et a inner join dt b on a.DPET_ID=b.deptid
where b.dep_name='IT'
nice !
Views are updatable.
How to create ReadOnly view or Non-updatable views?
+Kris Maly you can create UnionAll with some dummy table to make View readonly
+Awesome KJ
Your idea may be good
Could you please give me example
+Kris Maly You can try something like this :
create view vw_tblname
as
select col1, col2 from tbl1
Union all
select null,null where 1 = 0
I tried it and it does not let you delete , insert or update any data through view
Hi Venkat, have a question can we term VIEW tables as Marts as both might solve the same purpose??
wow nice explanation on view
HI Venkat sir,
Topics are getting easy but i want to practice those things. How can I practice those things?
I am a fresher graduate now
Thank you Sir :)
+Nimisha Jain You are very welcome Nimisha and thank you for the feedback. I am very glad you found the videos useful.
I have organised all the video tutorials in to playlists, which could be useful to you
ruclips.net/user/kudvenkatplaylists?view=1&sort=dd
If you need physical DVDs or you want to download all the videos for offline viewing please order them using the link below
www.pragimtech.com/kudvenkat_dvd.aspx
Slides and Text Version of the videos can be found on my blog
csharp-video-tutorials.blogspot.com
Tips to effectively use my youtube channel.
ruclips.net/video/y780MwhY70s/видео.html
If you want to receive email alerts, when new videos are uploaded, please subscribe to my youtube channel.
ruclips.net/user/kudvenkat
If you like these videos, please click the THUMBS UP button below the video.
We like to see these free video tutorials helping others as well. Please share the link with your friends and family who you think would also benefit from them.
Thanks
Venkat
I have to insert the data from the view different database to the table of another database by creating new column please guide me
why can't we just use the stored procedures instead of views? I mean both of them do the similar job right?
superb lecture
Question, what is the code to add an existing column to a view. not create a new column
I wish I could find a tutorial like this for C#!
Not sure if you have looked or not, but Venkat's channel has TONS of c# videos, all explained with equal clarity. I would strongly suggest subscribing.
Is age is a barrier to work on sql?, my age is 37 and i am looking for a Job related to sql.
Sir what's the difference between inline table valued functions and views. It's seems like they both work the same ... creating virtual tables
Inline allows parameter where as view wont allow
i need to create a script that auto likes your videos as soon as they load, if you teach us how we will run it
so what is non-updatable view then?
way of teaching is nice .
I have one question view was created by using 3 table,if i change anything in base tables it will reflect in View if do select all from view ?
best
Hello sir could you please publish materialized view video. It's highly needed.
Sir please make videos on SSRS or ReactJS
Would insert work with not null columns too?
+sandeep gupta devadula i have the same question. But in such cases to avoid errors i would set a default value for such columns in the base table. So when someone tries to insert using views those non null columns will get default values and avoid errrors.
+sandeep gupta devadula stackoverflow.com/questions/2648445/if-i-update-a-view-will-my-original-tables-get-updated check this out for answer to ur question.
Salman Shaikh thanks for the clarification
sandy294u glad to be of help! :)
I know its not relevant but HOW MUCH DO YOU EARN FROM YOUR Videos?
Also, please share your social media details i wanted to follow it.
Thanks...