CODEWITHSUNDEEP
sql-server-2008
Keeks
Keeks

Reputation: 269

Insert data into a view (SQL Server)

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?

Upvotes: 26

Views: 220157

Answers (9)

Delta
Delta

Reputation: 559

INSERT INTO dbo.rLicenses
    SELECT 'test'

Based on sqlhack in chapter "Data modifications through views"

Upvotes: 0

Umang Sharma
Umang Sharma

Reputation: 11

INSERT INTO viewname (Column name) values (value);

Upvotes: 0

NocFenix
NocFenix

Reputation: 701

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.

Upvotes: 4

Naresh Surisetty
Naresh Surisetty

Reputation: 11

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.

Upvotes: 1

Kamaldeep Goyal
Kamaldeep Goyal

Reputation: 1

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.

Upvotes: 0

Matthew Brewer
Matthew Brewer

Reputation: 31

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.

Upvotes: 3

Randy Minder
Randy Minder

Reputation: 48542

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?

Upvotes: 0

gbn
gbn

Reputation: 432742

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

Upvotes: 26

HLGEM
HLGEM

Reputation: 96658

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."

Upvotes: 4

Related Questions