insertable view in MS SQL 2008 with SP3

marrowyung
marrowyung used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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
DBA Freelancer
Commented:
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
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..
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Vitor MontalvãoIT Engineer
Distinguished Expert 2017
Commented:
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ãoIT Engineer
Distinguished Expert 2017

Commented:
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ãoIT Engineer
Distinguished Expert 2017

Commented:
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?
Top Expert 2012
Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial