The View Insert

Question

Post reply

The View Insert

Steve Jones - SSC Editor

SSC Guru

Points: 734418
More actions
July 13, 2016 at 10:19 pm

#77433

Comments posted to this topic are about the item The View Insert
July 13, 2016 at 11:52 pm

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Umer Akram SSC-Insane Points: 22209 More actions · Answer 1

Nice question, Reminded me one of the most asked question by interviewers in earlier days 🙂

morlindk SSCrazy Points: 2294 More actions · Answer 2

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?

tom.w.brannon Hall of Fame Points: 3956 More actions · Answer 3

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.

Ed Wagner SSC Guru Points: 287024 More actions · Answer 4

Nice question to get the brain working early.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 5

Great question, thanks.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Sean Lange SSC Guru Points: 286573 More actions · Answer 6

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/

dhober Ten Centuries Points: 1084 More actions · Answer 7

Definitely interesting...I thought you could only insert into views if the view was schema-bound. Hooray new knowledge!

Luis Cazares SSC Guru Points: 183698 More actions · Answer 8

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.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Revenant SSC-Forever Points: 42467 More actions · Answer 9

Revenant

SSC-Forever

Points: 42467

July 14, 2016 at 8:38 am

#1890128

Very sneaky! 😉

Thanks, Steve!

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 10

You are all welcome, and glad I got a good one here. This was something I learned as well.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 11

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

Sean Lange SSC Guru Points: 286573 More actions · Answer 12

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/

Ken SSCoach Points: 17191 More actions · Answer 13

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!