July 13, 2016 at 10:19 pm
Comments posted to this topic are about the item The View Insert
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
July 13, 2016 at 11:52 pm
This was removed by the editor as SPAM
July 14, 2016 at 12:54 am
Nice question, Reminded me one of the most asked question by interviewers in earlier days 🙂
July 14, 2016 at 1:28 am
Really interesting - and by testing out, I both learned something and got it right.
Still, my preferred answer would be simply "Yes, it Works". - You input a row to the view, then cannot retrieve it by the view! Maybe it has to be like this, but is it really "fine", as well?
July 14, 2016 at 5:30 am
morlindk (7/14/2016)
Really interesting - and by testing out, I both learned something and got it right.Still, my preferred answer would be simply "Yes, it Works". - You input a row to the view, then cannot retrieve it by the view! Maybe it has to be like this, but is it really "fine", as well?
Agreed. I tried it out and was surprised to find that I could insert to the view but then the row could not be retrieved from the view. Good learning for me today.
July 14, 2016 at 5:33 am
Nice question to get the brain working early.
July 14, 2016 at 6:09 am
Great question, thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 14, 2016 at 7:41 am
Great question. Oddly after answering I decided to see if it really would correctly. I ran the create table and view scripts. Then ran the insert statement. It says 1 row affected. However, the table and the view don't return any values. Weird!!!
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 14, 2016 at 7:55 am
Definitely interesting...I thought you could only insert into views if the view was schema-bound. Hooray new knowledge!
July 14, 2016 at 8:30 am
Sean Lange (7/14/2016)
Great question. Oddly after answering I decided to see if it really would correctly. I ran the create table and view scripts. Then ran the insert statement. It says 1 row affected. However, the table and the view don't return any values. Weird!!!
That's because the views aren't using WITH CHECK OPTION which would deny any data modification if it goes out of the scope of the view. Of course, you might already know that, as well, as the code is inserting rows to the table not the view.
July 14, 2016 at 8:38 am
Very sneaky! 😉
Thanks, Steve!
July 14, 2016 at 9:30 am
You are all welcome, and glad I got a good one here. This was something I learned as well.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
July 14, 2016 at 11:30 am
Luis Cazares (7/14/2016)
Sean Lange (7/14/2016)
Great question. Oddly after answering I decided to see if it really would correctly. I ran the create table and view scripts. Then ran the insert statement. It says 1 row affected. However, the table and the view don't return any values. Weird!!!That's because the views aren't using WITH CHECK OPTION which would deny any data modification if it goes out of the scope of the view. Of course, you might already know that, as well, as the code is inserting rows to the table not the view.
My table has values in it specifically for the insert I just performed.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 14, 2016 at 12:32 pm
SQLRNNR (7/14/2016)
Luis Cazares (7/14/2016)
Sean Lange (7/14/2016)
Great question. Oddly after answering I decided to see if it really would correctly. I ran the create table and view scripts. Then ran the insert statement. It says 1 row affected. However, the table and the view don't return any values. Weird!!!That's because the views aren't using WITH CHECK OPTION which would deny any data modification if it goes out of the scope of the view. Of course, you might already know that, as well, as the code is inserting rows to the table not the view.
My table has values in it specifically for the insert I just performed.
Mine did not. It said it ran successfully but the tables are all empty. Weird.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 14, 2016 at 3:46 pm
My Schedules table has data in it but classes does not. I figured that this is because the BuildingSchedule view only returns columns from the Schedules table. I tried updating this view to return the BuildingID from Classes and left the other return columns alone and when I ran the insert again I get this error:
Msg 4405, Level 16, State 1, Line 1
View or function 'dbo.RoomCapacity' is not updatable because the modification affects multiple base tables.
Which makes sense.
Thanks for the question Steve!
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply