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.
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

marrowyungSenior Technical architecture (Data)Author Commented:
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
Jason clarkDBA FreelancerCommented:
If you want to insert data to a view, then the answer is no. If you want to insert data into the underlying table through view? The answer is then yes. SQL Server will allow you to insert data into the underlying table through a view with a condition.
To know more please go this one nicely written article. https://msdn.microsoft.com/en-us/library/ms175521.aspx

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
marrowyungSenior Technical architecture (Data)Author Commented:
"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..
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Vitor MontalvãoMSSQL Senior EngineerCommented:
is view can have data insert in SQL 2008 with SP3
Yes, with certain conditions:
- 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);
- The View need to have all NOT NULL fields from the table, so won't raise an error.
marrowyungSenior Technical architecture (Data)Author Commented:
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 ?
Vitor MontalvãoMSSQL Senior EngineerCommented:
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

marrowyungSenior Technical architecture (Data)Author Commented:
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 ?
Vitor MontalvãoMSSQL Senior EngineerCommented:
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?
marrowyungSenior Technical architecture (Data)Author Commented:
"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?
Anthony PerkinsCommented:
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?
You are confused, there is no support for that in SQL Server 2014 or 2016 (and you can read all about it in SQL Server BOL).  The only way a VIEW with multiple joins supports updates is by using an INSTEAD of TRIGGER, and you are still not doing an UPDATE to the VIEW but to tables.
marrowyungSenior Technical architecture (Data)Author Commented:
"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.
marrowyungSenior Technical architecture (Data)Author Commented:
tks all
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.