Menu
I have been trying to understand what is wrong with the following view, and unfortunately I was not able to find my answer anywhere, other than using triggers, which I would like to avoid. Given the following view, when I try to insert into it I get the error above, however if I remove the inner join to the Company table everything seems to work just fine:
CREATE VIEW [dbo].[vwCheckBookingToCheck]
WITH SCHEMABINDING
AS
SELECT [checkUser].[CheckID] ,
[checkUser].[CheckToTypeID] ,
[checkUser].[CheckNumber] ,
[checkUser].[CheckDate] ,
[checkUser].[CheckAmount] ,
[checkUser].[CheckStatusID] ,
[checkUser].[CheckAcceptedBy] ,
[checkUser].[CreatedBy] ,
[checkUser].[CreatedDateTime] ,
[checkUser].[CheckToUserID] [ToID],
[checkUser].[CheckFromCompanyID] [FromID],
[companyFrom].[CompanyName]
FROM [dbo].[CheckUser] [checkUser]
INNER JOIN [dbo].[Company] [companyFrom] ON [companyFrom].[CompanyID] = [checkUser].[CheckFromCompanyID]
UNION ALL
SELECT [checkCompany].[CheckID] ,
[checkCompany].[CheckToTypeID] ,
[checkCompany].[CheckNumber] ,
[checkCompany].[CheckDate] ,
[checkCompany].[CheckAmount] ,
[checkCompany].[CheckStatusID] ,
[checkCompany].[CheckAcceptedBy] ,
[checkCompany].[CreatedBy] ,
[checkCompany].[CreatedDateTime] ,
[checkCompany].[CheckToCompanyID] [ToID],
[checkCompany].[CheckFromCompanyID] [FromID] ,
[companyFrom].[CompanyName]
FROM [dbo].[CheckCompany] [checkCompany]
INNER JOIN [dbo].[Company] [companyFrom] ON [companyFrom].[CompanyID] = [checkCompany].[CheckFromCompanyID]
GO
Here is my insert, I am only inserting in [CheckUser] or [CheckCompany]:
INSERT INTO [dbo].[vwCheckBookingToCheck]
( [CheckToTypeID] ,
[CheckNumber] ,
[CheckDate] ,
[CheckAmount] ,
[CheckStatusID] ,
[CheckAcceptedBy] ,
[CreatedBy] ,
[CreatedDateTime] ,
[ToID] ,
[FromID]
)
SELECT 2,
'Test' , -- CheckNumber - varchar(255)
'2014-08-23 20:07:42' , -- CheckDate - date
1233 , -- CheckAmount - money
0 , -- CheckStatusID - int
1 , -- CheckAcceptedBy - int
1 , -- CreatedBy - int
'2014-08-23 20:07:42' , -- CreatedDateTime - datetime
1, -- ToID - int
1 -- FromID - int
CheckToTypeID is my check constraint, is there any way to make this view work with inner joins? Again, if I remove the inner joins I am able to get it to work, but I would like to keep them if possible.
I am using SQL Server 2012, any help is appreciated.
Thanks, Paul
261
To modify table data through a view. In Object Explorer, expand the database that contains the view and then expand Views. Right-click the view and select Edit Top 200 Rows. You may need to modify the SELECT statement in the SQL pane to return the rows to be modified.
We cannot insert or update data using view. The view is a virtual table. We can do those action, but it's effect on real table data too. View is like a virtual table which enable us to get information of multiple tables.
You can insert data through a single-table view if you have the Insert privilege on the view. To do this, the defining SELECT statement can select from only one table, and it cannot contain any of the following components: DISTINCT keyword.
You can insert, update, and delete rows in a view, subject to the following limitations: If the view contains joins between multiple tables, you can only insert and update one table in the view, and you can't delete rows. You can't directly modify data in views based on union queries.
This is a bit long for a comment.
I cannot readily find the 2012 documentation on this subject, but the SQL Server 2008 documentation is quite clear:
A view is considered an updatable partitioned view when the view is a set of SELECT statements whose individual result sets are combined into one using the UNION ALL statement. Each SELECT statement references one SQL Server base table.
You have two tables in the from clause, so it is not updatable. It is a read-only view. I am not aware that this was changed in 2012.
99
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
