It is possible to use SqlBulkCopy to insert information into a view over 2 tables?

c# sqlbulkcopy sql-server

Question

I am moving to use SqlBulkCopy to deal with large insertions into some databases. This has lead to great performance improvements for one db.

However, another targeted db table is a view composing 2 tables. Applying the single table code leads to "System.Data.SqlClient.SqlException: View or function 'dbo.vwParameter' is not updatable because the modification affects multiple base tables". It is possible to flatten the view into one table without causing many complications, though undesired because it saves a good bit disk space.

So is it possible to use SqlBulkCopy in this context, and I am just doing something wrong? Or if not, what might be a good work-around that preserves the view for reading?

Accepted Answer

It turned out that SqlBulkCopy ignored my instead of triggers by default. All I had to do was pass SqlBulkCopyOptions.FireTriggers to the SqlBulkCopy constructor.


Popular Answer

You can use view with SqlBulkCopy.
But I believe your problem is that the view that you have is not updatable.
There are certain requirements that view has to meet to be updatable.
You can find more info here http://msdn.microsoft.com/en-us/library/ms187956.aspx
Look for 'Updatable Views' section.

I would suggest you to populate each of those two tables directly, one at a time, if possible?



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why