Insert data into a view SQL Server

Question

I have the following setup   CREATE TABLE dbo Licenses    Id    int IDENTITY 1 1  PRIMARY KEY   Name  varchar 100    RUser nvarchar 128  DEFAULT USER NAME      GO  CREATE VIEW dbo rLicenses AS SELECT Name FROM   dbo Licenses WHERE  RUser   USER NAME    GO   When I try to insert data using the view     INSERT INTO dbo rLicenses VALUES   test     an error arises   Cannot insert the value NULL into column Id  table master dbo Licenses  column does not allow nulls  INSERT fails    Why doesn t the auto increment of the identity column work when trying to insert using the view and how can I fix it   Scenario is   The different users of the database should only be able to work with their own rows in that table  Therefore I am trying to use the view as a kind of security by checking the username  Is there any better solution

User · Answer

INSERT INTO viewname  Column name  values  value

User · Answer

Looks like you are running afoul of this rule for updating views from Books Online   INSERT statements must specify values for any columns in the underlying table that do not allow null values and have no DEFAULT definitions

User · Answer

What about naming your column   INSERT INTO dbo rLicenses  name  VALUES   test     It s been years since I tried updating via a view so YMMV as HLGEM mentioned   I would consider an  INSTEAD OF  trigger on the view to allow a simple INSERT dbo Licenses  ie the table  in the trigger

User · Answer

You have created a table with ID as PRIMARY KEY  which satisfies UNIQUE and NOT NULL constraints  so you can t make the ID as NULL by inserting name field  so ID should also be inserted   The error message indicates this

User · Answer

You just need to specify which columns you re inserting directly into   INSERT INTO  dbo   rLicenses    Name   VALUES   test     Views can be picky like that

User · Answer

Inserting  test  to name will lead to inserting NULL values to other columns of the base table which wont be correct as Id is a PRIMARY KEY and it cannot have NULL value

User · Answer

Go to design for that table  Now  on the right  set the ID column as the column in question  It will now auto populate without specification

User · Answer

What is your Compatibility Level set to  If it s 90  it s working as designed  See this article   In any case  why not just insert directly into the table

[sql-server-2008] Insert data into a view (SQL Server)

Examples related to sql-server-2008