We've created a view in 2008 R2. Provided below are the definitions for both the base table and the view.
Base table:
CREATE TABLE dbo.test(
fcrecid char(25) NOT NULL,
fctype char(1) NOT NULL,
flbomcntrl bit NOT NULL,
flmstcntrl bit NOT NULL,
flrtgcntrl bit NOT NULL,
fcjobno varchar(20) NOT NULL,
fcsono varchar(10) NOT NULL,
identity_column int IDENTITY(1,1) NOT NULL,
timestamp_column timestamp NULL,
)
View Definition:
CREATE VIEW dbo.testview
AS
SELECT fcrecid,
fctype,
flbomcntrl,
flmstcntrl,
flrtgcntrl,
fcjobno,
fcsono,
'Parent' AS parentid
FROM dbo.test
We've migrated the database from 2008 R2 to 2016 and attempted to perform an insertion into the view using the following insert query and we can able to insert without any problem.
Insert Query
INSERT INTO dbo.testview (fcrecid,fctype,flbomcntrl,flmstcntrl,flrtgcntrl,fcjobno,fcsono,parentid) values ('','A',0,0,0,'','000066','Parent')
We dropped the Testview, created a view with same view definition and attempted the same insert query, resulting in the below error.
Msg 4406, Level 16, State 1, Line 1 Update or insert of view or function 'testview' failed because it contains a derived or constant field.
If we opt to alter the view instead of dropping and recreating it, we can avoid encountering the error mentioned earlier
I understand that we're attempting to insert values into the 'parentid' column, which is a constant field and not supported in SQL Server 2016.
How does Insert is working without drop of view (just migrate from 2008 R2 to 2016) or simply altering a view without drop and create a view?
Can the behavior of the insert query in 2016 be replicated to mimic that of 2008 R2 by dropping and creating a view?
I'm aware that many suggestions advise using an 'Instead of' trigger to update the constant field within it or just remove constant field in query. However, we don't want to take that route.
Thanks.
However, we don't want to take that route
Why not?parentid
from yourINSERT
. It doesn't make any sense to be there anyway.INSERT
statements updated in your Dev/UAT environments now, in readiness for when you go live in your production environment. 2008R2 has been EoL for over 4 years now, so you've had plenty of time to test all this and get ready, if I am honest.