inserting data into views

Question

inserting data into views

pramany

SSC Eights!

Points: 945
More actions
September 29, 2011 at 8:29 pm

#259393

Hi Everyone,
I am new to Sql server....After deep research I found this would be better place to gain knowledge.
I am having issues while understanding 'how views work'.
I am trying to insert values into base tables through a view, But It doesn't allow me to do that.
This is the error I got:
Msg 4405, Level 16, State 1, Line 2
View or function 'vw_myview' is not updatable because the modification affects multiple base tables.
And also can some one please explain about views and types of views?
I really appreciate.

Viewing 15 posts - 1 through 15 (of 22 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

The error message says it all: you cannot update views that reference more than one table.

In order to be updatable, a view must comply to some restrictions, that you can find here: http://msdn.microsoft.com/en-us/library/ms187956.aspx?ppud=4. Look up "Updatable Views" in that document and you should find all the info you need.

Hope this helps

Gianluca

-- Gianluca Sartori

pramany SSC Eights! Points: 945 More actions · Answer 2

pramany

SSC Eights!

Points: 945

September 30, 2011 at 11:14 am

#1388941

thank you

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 3

Gianluca Sartori (9/30/2011)
The error message says it all: you cannot update views that reference more than one table.

Not entirely true, Gianluca. You just can't update more than one table's columns at a time, which from the error message is what he's trying to do.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Lowell SSC Guru Points: 323518 More actions · Answer 4

Evil Kraig F (9/30/2011)
Not entirely true, Gianluca. You just can't update more than one table's columns at a time, which from the error message is what he's trying to do.

Kraig you are alluding to the possiblity of an INSTEAD OF trigger on the view, to handle the logic of insertin/updating tables that are joined in the view, right?

in an instead of trigger, the logic can be written to insert/update the underlying tables, depedning on teh desired operation and if the joins will permit the data to be identified correctly.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

spaghettidba SSC Guru Points: 105732 More actions · Answer 5

Evil Kraig F (9/30/2011)
Gianluca Sartori (9/30/2011)
The error message says it all: you cannot update views that reference more than one table.
Not entirely true, Gianluca. You just can't update more than one table's columns at a time, which from the error message is what he's trying to do.

You mean that I could update the columns from only one of the tables and it would work directly, without a trigger?

I don't think it can be done, but if it can, well, I'd love to see that. Do you have an example?

-- Gianluca Sartori

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Lowell (9/30/2011)
Kraig you are alluding to the possiblity of an INSTEAD OF trigger on the view, to handle the logic of insertin/updating tables that are joined in the view, right?

No, I suspect he's referring to direct updates to the view

Gianluca Sartori (10/1/2011)
Evil Kraig F (9/30/2011)
Gianluca Sartori (9/30/2011)
The error message says it all: you cannot update views that reference more than one table.
Not entirely true, Gianluca. You just can't update more than one table's columns at a time, which from the error message is what he's trying to do.
You mean that I could update the columns from only one of the tables and it would work directly, without a trigger?
I don't think it can be done, but if it can, well, I'd love to see that. Do you have an example?

Yup. From the BoL page you referenced:

Updatable Views
You can modify the data of an underlying base table through a view, as long as the following conditions are true:
Any modifications, including UPDATE, INSERT, and DELETE statements, must reference columns from only one base table.

CREATE TABLE t1 (

id INT IDENTITY PRIMARY KEY,

Somedate DATETIME

)

CREATE TABLE t2 (

id INT IDENTITY PRIMARY KEY,

ReferenceID INT,

SomeOtherdate DATETIME

)

INSERT INTO t1 (Somedate) VALUES (GETDATE())

INSERT INTO t2 (ReferenceID, SomeOtherdate) VALUES (1,GETDATE())

CREATE VIEW v1 AS

SELECT Somedate , SomeOtherdate FROM t1 INNER JOIN dbo.t2 ON t1.id = t2.ReferenceID

UPDATE v1 SET Somedate = '2011/04/25';

UPDATE v1 SET SomeOtherdate = '2011/06/25';

The view references 2 tables. Each update only updates column from one table. Both work.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 7

GilaMonster (10/1/2011)
Lowell (9/30/2011)
Kraig you are alluding to the possiblity of an INSTEAD OF trigger on the view, to handle the logic of insertin/updating tables that are joined in the view, right?
No, I suspect he's referring to direct updates to the view
...trim quote and code...

Yep, I was. Thanks for creating the example Gail. Now, I just need to figure out why I'm checking the site at 4:30 in the morning... :hehe:

Now, as to triggering on a view... I'll get back to you on that Lowell. I've both never thought to think about doing that, and had no idea you could.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

spaghettidba SSC Guru Points: 105732 More actions · Answer 8

Evil Kraig F (10/1/2011)
GilaMonster (10/1/2011)
Lowell (9/30/2011)
Kraig you are alluding to the possiblity of an INSTEAD OF trigger on the view, to handle the logic of insertin/updating tables that are joined in the view, right?
No, I suspect he's referring to direct updates to the view
...trim quote and code...
Yep, I was. Thanks for creating the example Gail. Now, I just need to figure out why I'm checking the site at 4:30 in the morning... :hehe:
Now, as to triggering on a view... I'll get back to you on that Lowell. I've both never thought to think about doing that, and had no idea you could.

Thanks Craig and Gail! I learned something new!

-- Gianluca Sartori

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Evil Kraig F (10/1/2011)
Now, as to triggering on a view... I'll get back to you on that Lowell. I've both never thought to think about doing that, and had no idea you could.

It's how you handle more complex inserts/updates/deletes, that do affect more than one table, or DML on non-updateable views

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

vince_sql Right there with Babe Points: 722 More actions · Answer 10

You can only insert data using a view if its referencing just one table, otherwise its ambiguous exactly where you want to insert the data.

http://sqlvince.blogspot.com/[/url]

pramany SSC Eights! Points: 945 More actions · Answer 11

but i am trying to insert data on multiple base tables in a view.

Lowell SSC Guru Points: 323518 More actions · Answer 12

pramany (10/1/2011)
but i am trying to insert data on multiple base tables in a view.

in order to insert into that view then, you would have to put an instead of trigger on the view, and define the logic for inserting into each of the underlying tables.

if you post the actual table definitions and the view definition, we might be able to help further.

post the actual CREATE TABLE and CREATE VIEW definitions for definitive assistance.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

nigel. SSChampion Points: 11636 More actions · Answer 13

Lowell (10/2/2011)
pramany (10/1/2011)
but i am trying to insert data on multiple base tables in a view.
in order to insert into that view then, you would have to put an instead of trigger on the view

Or multiple INSERTs/UPDATEs

Nigel
How to post questions

pramany SSC Eights! Points: 945 More actions · Answer 14

sorry to respond lately..

For creating instead of insert trigger on view.i am getting the following message

and the code that i prepared is

create trigger sampletrigger

on sample view

instead of insert

as

insert into basetable1

select name,id from inserted

insert into basetable2

select result from inserted

error : msg 213, Level 16, State 1, Procedure sampletrigger, Line 5

Column name or number of supplied values does not match table definition.

can some one help me in writing instead of insert trigger on view

thank you