Modifying Data in Partitioned Views
If a partitioned view is not updatable, it can serve only as a read-only copy of the original table. An updatable partitioned view can exhibit all the capabilities of the original table.
A view is considered an updatable partitioned view when the view is a set of SELECT statements whose individual result sets are combined into one using the UNION ALL statement. Each SELECT statement references one SQL Server base table. The table can be either a local table or a linked table referenced by using a four-part name, the OPENROWSET function, or the OPENDATASOURCE function (you cannot use an OPENDATASOURCE or OPENROWSET function that specifies a pass-through query).
Additionally, data modification statements referencing the view must follow the rules defined for INSERT, UPDATE and DELETE statements.
Note
Bulk importing into a partitioned view is not supported by the bcp command, or the BULK INSERT statement, or the INSERT ... SELECT * FROM OPENROWSET(BULK...) statement. However, you can insert multiple rows into a partitioned view by using an INSERT statement.
If the partitioned view is distributed across servers, avoid using triggers or cascading actions on the base tables. A trigger or cascading action could make changes to the underlying data that may affect the view definition.
Distributed partitioned views can only be updated if the user has CONTROL, ALTER, TAKE OWNERSHIP, or VIEW DEFINITION permission on each table underlying the view. For more information, see Troubleshooting Metadata Visibility of Distributed Partitioned Views.
Note
You can modify data through a distributed partitioned view only if you install SQL Server 2008 Enterprise or SQL Server 2008 Developer. However, you can modify data through a local partitioned view on any edition of SQL Server 2008.
INSERT Statements
INSERT statements add data to the member tables through the partitioned view. The INSERT statements must follow to these rules:
All columns must be included in the INSERT statement even if the column can be NULL in the base table or has a DEFAULT constraint defined in the base table.
The DEFAULT keyword cannot be specified in the VALUES clause of the INSERT statement.
INSERT statements must supply a value that satisfies the logic of the CHECK constraint defined on the partitioning column for one of the member tables.
INSERT statements are not allowed if a member table contains a column with an identity property.
INSERT statements are not allowed if a member table contains a timestamp column.
INSERT statements are not allowed if there is a self-join with the same view or any one of the member tables.
UPDATE Statements
UPDATE statements modify data in one or more of the member tables through the partitioned view. The UPDATE statements must follow to these rules:
UPDATE statements cannot specify the DEFAULT keyword as a value in the SET clause even if the column has a DEFAULT value defined in the corresponding member table
The value of a column with an identity property cannot be changed: however, the other columns can be updated.
The value of a PRIMARY KEY cannot be changed if the column contains text, image, or ntext data.
Updates are not allowed if a base table contains a timestamp column.
Updates are not allowed if there is a self-join with the same view or any one of the member tables.
DELETE Statements
DELETE statements remove data in one or more of the member tables through the partitioned view. DELETE statements are not allowed when there is a self-join with the same view or any one of the member tables.