Problem
Please present step-by-step instructions for causing a view to change the rows it displays even when its is_updatable property is No. Demonstrate how to accomplish this with T-SQL inserts and deletes for views of the underlying tables for a view. Also, demonstrate T-SQL code for succinctly inserting multiple rows into the rows displayed by a view when its is_updatable property is No.
Solution
The SQL Docs site offers a detailed summary of some general rules for when a view is updatable, but the site does not provide a walkthrough of how to update the data that a view displays. This tip walks you through the steps for a relatively simple way of inserting and deleting rows from a view whose is_updatable property is No. All changes to data sources are implemented through views. By enabling changes through views, a DBA can allow users to make some changes to a table without granting unrestricted access to all the data in a source table, such as salary or personal identity information like birth date.
A sample view based on joins between multiple tables
This tip builds on a prior tip that utilized a view involving joins among four tables. This type of view often has an is_updatable property of No.
Below is a script for creating the view. This tip, like the earlier one in this series on views, uses the CodeModuleTypes database for demonstration purposes. The earlier tip populates dbo.employees, dbo.persons, dbo.employeedepthistory, and dbo.departments tables in the CodeModuleTypes database from the AdventureWorks2014 database. The copying of data from the AdventureWorks2014 database to the CodeModuleTypes database permits changes to be made to data in the CodeModuleTypes database without altering the data in the AdventureWorks2014 database. Additionally, the copied tables are joined to form the data source for the employee_persons view in the CodeModuleTypes database.
The dbo.employees table is the primary base table for the view. All other tables in the from clause for the select statement within the view join to this table either directly or indirectly.
The dbo.persons and dbo.employeedepthistory tables join to the dbo.employees table by BusinessEntityID column values. These are examples of a direct join to the dbo.employees table.
The dbo.departments table joins to the dbo.employeedepthistory table by departmentID column values. The dbo.departments table serves a lookup role for mapping departmentID numeric values from the dbo.employeedepthistory table to department names in the dbo.departments table. The dbo.departments table joins indirectly to the dbo.employees table through the dbo.employeedepthistory table.
Tracking Departments in the CodeModuleTypes database
This section demonstrates how to add a new department named Analytics to the dbo.departments table. You can start by creating a view named dbo.v_departments. Then, use the dbo.v_departments view to add a new row to the dbo.departments table. The script for accomplishing and verifying these steps appears below.
The code starts by removing any prior version of the dbo.v_departments view.
Next, a create view statement makes a fresh copy of the dbo.v_departments view based on the dbo.departments table.
The next block of three statements illustrates and confirms the process for adding a row to the dbo.departments table through the dbo.v_departments view.
The first statement is a select statement that can confirm there is no department with a name of Analytics.
The second statement illustrates the syntax for adding a row with a name value of Analytics through the view to the table. There is no need to assign a value to the departmentID column because it has an identity property.
The third statement is another select statement that can confirm the addition of a new row with a name column value of Analytics in the dbo.departments table after the insert into statement.
The following script ends with a select statement for the department names in the dbo.departments table.
Here’s the output from the last select statement in the prior script.
The first sixteen rows are from the original departments table in the AdventureWorks2014 database.
The seventeenth row is for the Analytics department inserted through dbo.v_departments in the preceding script.
Entering a new employee in the Analytics Department
The preceding section sets up a new department. This section will insert a new employee to the dbo.employees table using the employee_persons view. Notice that this is a different approach than the one used in the preceding section.
A department was added by creating a new view named dbo.v_departments based on the dbo.departments table, and then inserting a row into the table through the new view.
The approach illustrated in this section uses the original view based on all four base tables. However, the insert is only for columns of the dbo.employees table – not all the columns of the dbo.employee_persons view.
The code for inserting a new employee and for confirming the outcome appears next.
The script begins with a count of the rows in the dbo.employees table. Presuming you are starting with a freshly copied version of the employees table from the AdventureWorks2014 database, the count will be 290 – the number of employees before any new employees are added.
The next statement is an insert into statement for dbo.employee_persons. The inserted column values (BusinessEntityID, OrganizationLevel, and JobTitle) are just for the dbo.employees base table, which is the primary base table of the dbo.employee_persons view.
The third statement in the script is for second count of the rows in the dbo.employees table. If the insert into is successful, the second count should be one more than the first count.
The script concludes with two more select statements.
The first one displays the row values added to the dbo.employees table.
The second one is to display row values in the dbo.employee_persons view with a value matching the BusinessEntityID value inserted with the preceding insert into.
The next screen shot displays the results set from each of the four select statements in the preceding script.
The first pane shows the outcome from the select statement before the insert into statement. The count of employees is 290.
The second pane shows the count from the select statement immediately after the insert into statement. Because the insert into statement added a single employee, the count of employees is 291.
The third pane shows a results set based on the dbo.employees table. The where clause for the select statement specifies the BusinessEntityID value (30001) specified in the previously specified insert into statement. The column values in the results set corresponds to the set of three values specified in the prior insert into statement.
The fourth pane shows the results set for a BusinessEntityID value of 30001 from the dbo.employee_persons view. Notice that no row values are returned. This is because all the base table row values are not yet specified for a BusinessEntityID value of 30001. Therefore, the select statement cannot return a results set.
You may be wondering why you inserted into the dbo.employee_persons view, but the code populated the dbo.employees table. The answer is that the dbo.employees table rows have BusinessEntityID values that perfectly match the BusinessEntityID values from the dbo.employee_persons view. I tested another approach which added a row via a view based just on the dbo.employees table. That approach yields the same outcome as an insert into for the dbo.employee_persons view.
Inserting FirstName and LastName values for the dbo.persons table
The FirstName and LastName for employees is derived from the dbo.persons table in the CodeModuleTypes database. This section demonstrates two attempts to insert the FirstName and LastName values for the BusinessEntityID value specified for the employee in the preceding section.
The first attempt tries an insert of values directly into the dbo.employee_persons view. The outcome from the script below confirms that this attempt fails.
The second attempt tries an insert of values into a view (dbo.v_persons) based on the dbo.persons table. The results set for this second try succeeds.
The code in the script below illustrates the implementation of the first and second tries as well as select statements to confirm the outcome of the tries.
The first three statements include an insert into the dbo.employee_persons view for a new person named Iam Analytics. This statement is surrounded by select statements before and after it. The count resulting from each select statement is the same (19972); this count is the initial count of persons in the AdventureWorks2014 database. Obviously, the persons tracked by the database extend beyond just employees.
The next block of code creates a new view named dbo.v_persons. This view is based on the dbo.persons table. After the create view statement succeeds, a select statement displays a message confirming the creation of the new view.
Next, an insert into statement for the dbo.v_persons view makes a second attempt to add FirstName and LastName values into the dbo.persons table. This attempt succeeds as evidenced by the count of rows from the trailing select statement being one greater for the count from the preceding select statement.
The last two select statements in the script confirm that the new FirstName and LastName values for the person with a BusinessEntityID value of 30001 are added to the results set for a select statement based on the dbo.persons table but are not added to the results set for the dbo.employee_persons view. There are still some unspecified values for the select statement in the defining code for the employee_persons view. These values need to be specified to generate a results set for a new employee from the view.
Inserting DepartmentID and EndDate values for a BusinessEntityID value of 30001
As indicated at the end of the preceding section, the dbo.employee_persons view requires the specification of more fields to allow it to return a results set for a new row. A departmentID field is one of these fields. This field has a smallint data type that is referenced in a join between the dbo.departments table and the dbo.employeedepthistory table for the select statement in the view’s definition. The second field has a name of EndDate. The dbo.employeedepthistory table column value for EndDate must be null for the row to be evaluated as representing a current employee in the departmentID field value.
The insert into statement in the script below adds the departmentID and EndDate column values to the dbo.employeedepthistory table through the dbo.v_edh view. The dbo.v_edh view is based on the dbo.employeedepthistory table in the same way that the dbo.v_persons view is based on the dbo.persons table. The dbo.v_edh view facilitates the insertion of four column values.
The next script shows how to insert four values into the columns of the dbo.employeedepthistory table through the dbo.v_edh view.
BusinessEntityID has a value of 30001, which designates the employee currently being added to the table through the view.
DepartmentID is 17, which is the numeric indicator value for the Analytics department.
2019-08-01 is the start date when the employee came onboard within the department.
The value of Null for end date indicates the employee still works in the department – that is, the employee has not yet left the department for another assignment.
A short and sweet way to insert multiple rows into tables from views
The preceding screen shot confirms that it is possible to insert data into tables via views so that a view based on joins between multiple data sources can show new data based on its joined data sources. The results in the preceding screen shot also confirm that you can change the rows returned by view even when the is_updatable property for the view is No.
While the preceding sections showed a problem and a workaround, you may be thinking that the approach takes more effort than you want to spend on the task. The truth is that the amount of code for the task can be very short and sweet. All you need beforehand are the source tables and views for populating the underlying tables for your display view. The creation of the data and views is a one-time task. The code in the preceding sections illustrate how to create most of the views you need. What’s left out is just to conserve space, but if you understand what’s shown, you can easily create your own version of the dbo.v_edh view (or just request the code in a comment for this tip, and I will reply with the code).
Next, you may want to delete the previously added row to the employee_persons view. You can do that with the following script. By the way, the following code shows how easy it is to delete rows through views.
By: Rick Dobson