Cant insert data through view

I am a database administrator at my company.
Users report that insert statements against the view named
Production.viewTest do not succeed. But i firmed that the insert
statements that are being used are valid.
The only table that the view uses is named Production.ExpiredProduct.
The table has the following definition:
CREATE TABLE Production.ExpiredProduct (
ExpiredProductID INT IDENTITY CONSTRAINT PK_EXPIREProduct PRIMARY
KEY,
Name NVARCHAR(50) NOT NULL,
ListPrice MONEY NULL)
How can i ensure that insert statement can be complete successfull by
using SQL Server Management Studio (SSMS).Any possible issue like
"Schema","Indexes", or "Statictis" caused this problem?Hi
What does not mean "does not work"? What is the error? Why would you want to
use SSMS rather than Query Builder?
<chaukimwai1978@.gmail.com> wrote in message
news:1177236011.207593.66000@.b58g2000hsg.googlegroups.com...
>I am a database administrator at my company.
> Users report that insert statements against the view named
> Production.viewTest do not succeed. But i firmed that the insert
> statements that are being used are valid.
> The only table that the view uses is named Production.ExpiredProduct.
> The table has the following definition:
> CREATE TABLE Production.ExpiredProduct (
> ExpiredProductID INT IDENTITY CONSTRAINT PK_EXPIREProduct PRIMARY
> KEY,
> Name NVARCHAR(50) NOT NULL,
> ListPrice MONEY NULL)
> How can i ensure that insert statement can be complete successfull by
> using SQL Server Management Studio (SSMS).Any possible issue like
> "Schema","Indexes", or "Statictis" caused this problem?
>|||> How can i ensure that insert statement can be complete successfull by
> using SQL Server Management Studio (SSMS).
You can't ensure the insert will succeed but you can test the insert by
running an insert from a query window. Assuming your view DDL is:
CREATE VIEW Production.viewTest
AS
SELECT
ExpiredProductID,
Name,
ListPrice
FROM Production.ExpiredProduct
GO
You can test with something like:
INSERT INTO Production.viewTest (Name, ListPrice)
VALUES('test', 1.0)
GO
The insert will fail if you try to specify an explicit identity value for
ExpiredProductID (unless you turn on IDENTITY_INSERT):
INSERT INTO Production.viewTest (ExpiredProductID, Name, ListPrice)
VALUES(1, 'test', 1.0)
Hope this helps.
Dan Guzman
SQL Server MVP
<chaukimwai1978@.gmail.com> wrote in message
news:1177236011.207593.66000@.b58g2000hsg.googlegroups.com...
>I am a database administrator at my company.
> Users report that insert statements against the view named
> Production.viewTest do not succeed. But i firmed that the insert
> statements that are being used are valid.
> The only table that the view uses is named Production.ExpiredProduct.
> The table has the following definition:
> CREATE TABLE Production.ExpiredProduct (
> ExpiredProductID INT IDENTITY CONSTRAINT PK_EXPIREProduct PRIMARY
> KEY,
> Name NVARCHAR(50) NOT NULL,
> ListPrice MONEY NULL)
> How can i ensure that insert statement can be complete successfull by
> using SQL Server Management Studio (SSMS).Any possible issue like
> "Schema","Indexes", or "Statictis" caused this problem?
>|||1) We need more information to assist you properly. At a minimum we would
need the view definition, the INSERT statement that fails as well as the
error reported after the failure.
2) From BOL:
Updatable Views
You can modify the data of an underlying base table through a view, as long
as the following conditions are true:
a.. Any modifications, including UPDATE, INSERT, and DELETE statements,
must reference columns from only one base table.
b.. The columns being modified in the view must directly reference the
underlying data in the table columns. The columns cannot be derived in any
other way, such as through the following:
a.. An aggregate function: AVG, COUNT, SUM, MIN, MAX, GROUPING, STDEV,
STDEVP, VAR, and VARP.
b.. A computation. The column cannot be computed from an expression that
uses other columns. Columns that are formed by using the set operators
UNION, UNION ALL, CROSSJOIN, EXCEPT, and INTERSECT amount to a computation
and are also not updatable.
c.. The columns being modified are not affected by GROUP BY, HAVING, or
DISTINCT clauses.
d.. TOP is not used anywhere in the select_statement of the view together
with the WITH CHECK OPTION clause.
INSTEAD OF Trigger is a possible alternative to attempting to directly aply
DML to a view.
TheSQLGuru
President
Indicium Resources, Inc.
<chaukimwai1978@.gmail.com> wrote in message
news:1177236011.207593.66000@.b58g2000hsg.googlegroups.com...
>I am a database administrator at my company.
> Users report that insert statements against the view named
> Production.viewTest do not succeed. But i firmed that the insert
> statements that are being used are valid.
> The only table that the view uses is named Production.ExpiredProduct.
> The table has the following definition:
> CREATE TABLE Production.ExpiredProduct (
> ExpiredProductID INT IDENTITY CONSTRAINT PK_EXPIREProduct PRIMARY
> KEY,
> Name NVARCHAR(50) NOT NULL,
> ListPrice MONEY NULL)
> How can i ensure that insert statement can be complete successfull by
> using SQL Server Management Studio (SSMS).Any possible issue like
> "Schema","Indexes", or "Statictis" caused this problem?
>