SQL: Update or insert of view or function 'xxx' failed because it contains a derived or constant field

In a recent forum post, the OP was asking about an issue where he had two tables "users" and "userroles" and was getting the following error when he tried to insert rows into the "userroles" table:

Msg 4406, Level 16, State 1, Line 1
Update or insert of view or function 'UserRoles' failed because it contains a derived or constant field.

Now the first thing that was odd about this was that the error message was clearly showing that UserRoles wasn't a table. It's either a view or a table-valued function (TVF). An insert into a TVF isn't going to work, so I presume this is a view.

Now it's saying that it contains a derived or constant field. I have no idea what the table was actually called, so let's create one to try this:

Then an insert should be fine:

Running it returns the two rows inserted as expected.

How let's add some derived things. First, I'll add a derived column to the view and try the insert again:

Adding a derived column in the view works just as expected. What about adding it to the table instead?

I've set the view so that it doesn't use that derived column, and all is still good. But what if the view references the derived column?

And that's all good too. So what does that leave us?

Well a common view definition that I often come across is where the view returns data from a table, along with a constant row. Perhaps it's a "superuser" or something in this case. So let's try that:

And there's the error:

Msg 4406, Level 16, State 1, Line 78
Update or insert of view or function 'dbo.UserRoles' failed because it contains a derived or constant field.

You can't insert through a view when you have only this type of setup.

 

Leave a Reply

Your email address will not be published.