Inserting into View - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Inserting into View

Author

Topic

Sirchrisak
Starting Member

19 Posts

Posted - 2005-07-27 : 12:46:13

I have some base tables from which I created a view.I want to insert into these base tables by inserting into the view, my problem is that it gives an error; incorrect syntax. Here is my script for creating the view and inserting into the view

CREATE       view AccountPayment         
As
 
SELECT TransactionTypeId,AccountNumber,PaymentTypeId, Accounts.TransactionId,TransactionClassId,TransactionOwnerId,TransactionDate,[Description]
FROM [Transaction],Accounts
where PaymentTypeId=2
with check option

insert into  AccountPayment (1,2378PK, 2, 2, 3, '2-04-05', 'somestuff' )

it is giving me error of incorect syntax near PK

Chris

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-07-27 : 12:51:21

'2378PK',
notice the ---> ' <----

rockmoose

Lakeside
Starting Member

5 Posts

Posted - 2005-07-27 : 13:23:03

Insert Into AccountPayment
(TransactionTypeId,AccountNumber,PaymentTypeId,TransactionId,TransactionClassId,TransactionOwnerId,TransactionDate,[Description])
Values
(1,2378PK, 2, 2, 3, '2-04-05', 'somestuff' )

Ravenn
Starting Member

7 Posts

Posted - 2005-07-27 : 14:50:11

uhh.... don't you need a trigger to insert into view?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-07-27 : 14:52:54

quote:
Originally posted by Ravenn

uhh.... don't you need a trigger to insert into view?

No.

Tara

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-07-27 : 16:12:19

I would think you'd need a trigger in this case ... this view is a cross product between two tables! I haven't tried it, but that can't possibly be something you can INSERT into or UPDATE, can it?

- Jeff

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-07-27 : 16:19:23

Yes in this case you would as you can't insert into a view when more than one table is involved. I was, however, answering the specific question and not as related to this thread which I should have elaborated although I didn't notice his/her view included more than one table as my eyes will see more than one table when the JOIN syntax is used.

Tara

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2005-07-27 : 16:23:06

That's right Jeff.

SQL Server can only INSERT/UPDATE/DELETE views that reference a single table in the FROM clause.
Anything else requires INSTEAD OF triggers.

DavidM

A front-end is something that tries to violate a back-end.

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2005-07-27 : 16:25:26

SNIPED!

No wonder you are at 11K Tara!

DavidM

A front-end is something that tries to violate a back-end.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-07-27 : 16:28:29

EDIT:

The UPDATE / INSERT / DELETE can only affect 1 base table
in the view.
So You can modify views that reference several tables (in a way at least)
Even CROSS JOINS.

rockmoose

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-07-27 : 16:54:11

I'm not sure what your point is moose. What part are you disagreeing with that David and I said?

Tara

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-07-27 : 17:14:05

quote:
Originally posted by tduggan

I'm not sure what your point is moose. What part are you disagreeing with that David and I said?

Tara

I thought you guys meant that there can't be a JOIN clause
in the view definition. (or implicit "old style" joins).
-- At least if the view is going to be updateable wo iof triggers --

Did I misinterpret?

rockmoose

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2005-07-27 : 17:33:29

Hmmm. I am confused rockmoose.

My understanding is that if the view contains more than one table in the FROM clause (this includes any type of JOIN operation) the view MUST have INSTEAD OF triggers to be able to updated.

DavidM

A front-end is something that tries to violate a back-end.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-07-27 : 17:48:50

No, it can be inserted/updated as long as only 1 base table is affected. (delete not possible).
You usually do WITH CHECK, might cause violations.

You can try it out:

create table x(a int primary key)
create table y(b int primary key)
go
create view xy as select a,b from x,y -- cross join view
go

insert xy(a) select 1
insert xy(a) select 2
insert xy(b) select 3
insert xy(b) select 4
select * from xy

update xy set a=b 	--<-- fails on pk
go

update xy set a=a+b 	--<-- ok
select * from xy
go

update xy set a=2,b=2 	--<-- fails on multiple base tables
go

drop table x
drop table y
drop view xy

rockmoose

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2005-07-27 : 18:05:04

Wow! I am not at my dev box.. so I'll try later.

Does it work if you mark the view WITH CHECK?

You learn something new everyday....

DavidM

A front-end is something that tries to violate a back-end.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-07-27 : 18:33:14

The example I posted is really simple, so it works with the WIH CHECK OPTION.
In real life, I guess it will effectively cause inserts+updates on these kinds of views to fail.

I don't really use that option, maybe I should...

create table x(a int primary key)
create table y(b int null)
go
create view xy as select a,b from x,y where a=b or b is null
go

insert xy(a) select 1
insert xy(b) select 1
insert xy(b) select 1
insert xy(b) select 1
select * from xy

update xy set b=null 	--<-- update the many side
select * from xy

--drop table x;drop table y;drop view xy

a           b           
----------- ----------- 
1           1
1           1
1           1

a           b           
----------- ----------- 
1           NULL
1           NULL
1           NULL

rockmoose

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2005-07-27 : 18:43:15

>>I don't really use that option, maybe I should...

That's all I use! Maybe I shouldn't!

DavidM

A front-end is something that tries to violate a back-end.

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2005-07-27 : 19:47:41

OK... I have had a little chance to play with this and it is seriously twisted..


create table x(a int primary key)
create table y(b int null)
go
create view xy as select a,b from x,y where a=2 
go
create view xy_CHECK as select a,b from x,y where a=2 WITH CHECK OPTION
go
--Inserts first (Not checked)
insert xy(a) select 2 -- OK
insert xy(a) select 3 -- OK But should FAIL!!!!
go
--start again
delete x
delete y
go
--CHECKED view
insert xy_CHECK(a) select 2 --NO -- But should have WORKED! 
insert xy_CHECK(b) select 1 -- NO
go
--Try again with rows in y -- y must not be empty!?!?!?!?!?!?
insert xy(b) select 1 -- OK
insert xy_CHECK(a) select 2 --Yes!
insert xy_CHECK(b) select 1 -- Yes!
go
--Updates
UPDATE xy_CHECK set b = 100 -- OK
go
UPDATE xy_CHECK set a = 1 -- BAD
go
UPDATE xy set a = 1 -- OK But should have FAILED
go
select * from xy
select * from xy_CHECK
select * from y
select * from x

--drop table x;drop table y;drop view xy, xy_CHECK

The empty set of Y completely shattered the CHECK clause...DAMN!

The violation of the views constraint is unnaceptable IMO and I would be VERY hard pressed to expose it to end users.... That's why I like the CHECK option, it allows end users to throw shit at it while maintaing integrity...

Thanks for the lesson rockmoose!

DavidM

A front-end is something that tries to violate a back-end.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-07-28 : 07:56:06

>> insert xy(a) select 3 -- OK But should FAIL!!!!
I can't see that. Because of the where a = 2 ?, It doesn't check those kind of predicates afaik.

Yes, this was fishy.

It's always possible to create iof triggers with immediate raiserror/return on views that are not used for insert/update,
and prevent the user from doing anything except selecting from them.

This was all interesting, I think You tend to use views more than me though.
When I use views it's mostly only for SELECT, certainly that is the case if the view involves a JOIN.
Views that I design for update/insert are simple ones that are just a projection + restriction from 1 base table.

rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources