Link to home
Start Free TrialLog in
Avatar of marrowyung
marrowyung

asked on

insertable view in MS SQL 2008 with SP3

hi,

I have a question from a user that he want to insert data to a view in SQL server 2008 with SP3, is view can have data insert in SQL 2008 with SP3, I heard about indexed view as view can be indexed.

please suggest.
Avatar of marrowyung
marrowyung

ASKER

I am reading this :

http://stackoverflow.com/questions/4197674/insert-data-into-a-view-sql-server

and seems that we can insert data via view since on SQL Server 2014 and SQL Server 2016 ctp 2.3  ?

but this are telling me even SQL 2005 can do it ?:

https://msdn.microsoft.com/en-us/library/ms180800(v=sql.100).aspx
ASKER CERTIFIED SOLUTION
Avatar of Jason clark
Jason clark
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
"SQL Server will allow you to insert data into the underlying table through a view with a condition."

with condition ? the condition listed on my link above ?

so your link means use instead of trigger is the ONLY way to do it ?

but it must be  insert data into the underlying table through view, as we should not be able to insert data to a view directly..
SOLUTION
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
victor,

tks,

"You can only insert data into a columns from a single table. i.e. if the View is a join from many tables you can't INSERT rows (but you can UPDATE);"

any URL said that as well as we can update ? yes, we have a view inner join 5-6  tables and we are doing insert into.

"The View need to have all NOT NULL fields from the table, so won't raise an error. "

so still one table only and can only update ?
You can only insert if there's only one table in the View definition. For example this will work:
CREATE VIEW View_1
AS
SELECT column1, column2
FROM Table_1
GO

INSERT INTO View_1
VALUES ('ABC', '123')

Open in new window


But this don't:
CREATE VIEW View_2
AS
SELECT * 
FROM Table_1 T1
    INNER JOIN Table_2 T2 ON T1.Column1 = T2.Column1
GO

INSERT INTO View_2
VALUES ('ABC', '123')

Open in new window

Returns:
Msg 4405, Level 16, State 1, Line 21
View or function 'View_2' is not updatable because the modification affects multiple base tables.


Nevertheless you can update View_2 fields:
UPDATE View_2
SET column2='999'
WHERE column1='ABC'

Open in new window

tks. but any link to reference that ?

I think only instead of trigger can help.

https://msdn.microsoft.com/en-us/library/ms175521(v=sql.100).aspx

SQL server 2014 and 2016 can get ride of all them ?
If you're asking for a MSDN link here's an article but what I did above was a real test so you could see the results.

SQL server 2014 and 2016 can get ride of all them ?
Rid of what?
"Rid of what? "

I mean the insert into limitation, which mean SQL server 2014 and 2016 can remove the limitation on the View has more than one table inner join inside and we have to update them all by insert into view?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
"and you are still not doing an UPDATE to the VIEW but to tables"

but update is ok , right?

the online LInk above say partitioned view also ok. so 2 x methods

tks, anyway.
tks all