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.
CREATE VIEW dbo.employee_persons
AS
SELECT
em.BusinessEntityID,
em.OrganizationLevel,
em.JobTitle,
per.FirstName,
per.LastName,
d.DepartmentID,
d.Name Dept_Name
FROM dbo.employees em
INNER JOIN dbo.persons per ON em.BusinessEntityID = per.BusinessEntityID
LEFT JOIN dbo.employeedepthistory edh ON edh.BusinessEntityID = em.BusinessEntityID
INNER JOIN dbo.departments d ON d.DepartmentID = edh.DepartmentID
WHERE edh.EndDate IS NULL -- to keep only current department assignment
You can examine the is_updatable property for a view with a query like the following one.
SELECT *
FROM information_schema.views
WHERE table_name = 'employee_persons'
Here’s the output from the preceding query. Notice that is_updatable has a value of No.
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.
-- remove a prior version of dbo.v_Departments if it exists
BEGIN TRY
DROP VIEW dbo.v_Departments
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() [Error Number],
ERROR_MESSAGE() [Error Message]
END CATCH
GO
-- create dbo.v_Departments
CREATE VIEW dbo.v_Departments
AS
SELECT
DepartmentID,
Name
FROM dbo.departments
GO
-- before inserting a new department named Analytics through dbo.v_Departments view
SELECT * FROM dbo.departments WHERE Name = 'Analytics'
-- insert a new department named Analytics through dbo.v_Departments view
INSERT INTO dbo.v_Departments (Name) VALUES ('Analytics')
-- after inserting a new department named Analytics through dbo.v_Departments view
SELECT * FROM dbo.departments WHERE Name = 'Analytics'
-- Analytics department is added to dbo.departments table
SELECT * FROM dbo.departments ORDER BY DepartmentID
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.
-- count of dbo.employees table rows (290)
-- before inserting a new employee through employee_persons view
SELECT COUNT(*) [count of employees before insert] FROM dbo.employees
-- insert a row into the dbo.employee_persons view
INSERT INTO dbo.employee_persons (BusinessEntityID, OrganizationLevel, JobTitle)
VALUES (30001, 1, 'Chief Analytics Officer')
-- count of dbo.employees table rows (291) after inserting a new employee through employee_persons view
SELECT COUNT(*) [count of employees after insert] FROM dbo.employees
-- inserted record with BusinessEntityID = 30001 is in dbo.employees table
SELECT * FROM dbo.employees WHERE BusinessEntityID = 30001
-- inserted record with BusinessEntityID = 30001 is missing from dbo.employee_persons view
SELECT * FROM dbo.employee_persons WHERE BusinessEntityID = 30001
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.
-- count of dbo.persons table rows (19972)
-- before attempt to insert a new person into dbo.employee_persons view
SELECT COUNT(*) [count of persons before attempt to insert] FROM dbo.persons
-- insert a row into the dbo.employee_persons view for a new person but the attempt fails
INSERT INTO dbo.employee_persons (BusinessEntityID, FirstName, LastName)
VALUES (30001, 'Iam', 'Analytics')
-- count of dbo.persons table rows (19972) after attempt to insert a new person into dbo.employee_persons view
SELECT COUNT(*) [count of persons after attempt to insert] FROM dbo.persons
-- remove prior version of dbo.v_persons, if there is one
BEGIN TRY
DROP VIEW dbo.v_persons
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() [Error Number],
ERROR_MESSAGE() [Error Message]
END CATCH
GO
-- create dbo.v_persons
CREATE VIEW dbo.v_persons
AS
SELECT
BusinessEntityID,
FirstName,
LastName
FROM dbo.persons
GO
-- feedback that the dbo.v_persons view was created
SELECT 'Created dbo.v_persons to insert a new person' [feedback]
-- count of dbo.persons table rows (19972)before inserting a new person
SELECT COUNT(*) [count of persons before attempt to insert] FROM dbo.persons
-- insert a row into the dbo.v_persons view for a new person the attempt succeeds for insert into this view
INSERT INTO dbo.v_persons (BusinessEntityID, FirstName, LastName)
VALUES (30001, 'Iam', 'Analytics')
GO
-- count of dbo.persons table rows (19973) after inserting a new person
SELECT COUNT(*) [count of persons after attempt to insert] FROM dbo.persons
-- inserted record with BusinessEntityID = 30001 is in dbo.persons table
SELECT * FROM dbo.persons WHERE BusinessEntityID = 30001
-- inserted record with BusinessEntityID = 30001 is missing from dbo.employee_persons view
SELECT * FROM dbo.employee_persons WHERE BusinessEntityID = 30001
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.
-- insert new record with BusinessEntityID = 30001 into dbo.employeedepthistory table through dbo.v_edh view
INSERT INTO dbo.v_edh (BusinessEntityID, DepartmentID, StartDate, EndDate)
VALUES (30001, 17, '2019-08-01', NULL)
-- record with BusinessEntityID = 30001 is present insert
SELECT * FROM dbo.employeedepthistory WHERE BusinessEntityID = 30001
-- inserted record with BusinessEntityID = 30001 is no longer missing from dbo.employee_persons view!
SELECT * FROM dbo.employee_persons WHERE BusinessEntityID = 30001
The insert into in the preceding script completes the set of values required by the select statement in the definition of the employee_persons view. As a result, the final select statement from the preceding can display a results set row based on the view for a BusinessEntityID value of 30001. The next screen shot displays the results set below for the values inserted into underlying tables through views for the dbo.employee_persons view.
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.
-- delete employee inserted in a tutorial style
DECLARE @BusinessEntityID int = 30001
DELETE FROM dbo.v_edh WHERE BusinessEntityID = @BusinessEntityID
DELETE FROM dbo.v_persons WHERE BusinessEntityID = @BusinessEntityID
DELETE FROM dbo.v_employees WHERE BusinessEntityID = @BusinessEntityID
After restoring the original source data by deleting the previously added row, you can insert new rows via a script prepared along the lines of the following code. The following script demonstrates how to populate base tables via views for two freshly inserted rows for the dbo.employee_persons view.
- It takes just three insert into statements for each fresh row of output from the employee_persons view.
- The first fresh row of data is for a person named Iam Analyst with a job title of Chief Analytics Officer.
- The second fresh row of data is for a person named Iam Sr. Data Scientist with a job title of Sr. Data Scientist.
-- insert two fresh rows into dbo.employee_persons views from previously created views and base tables
-- add the Chief Analytics Officer to the Analytics department insert a row into the dbo.employee_persons view for a new employee
INSERT INTO dbo.employee_persons (BusinessEntityID, OrganizationLevel, JobTitle)
VALUES (30001, 1, 'Chief Analytics Officer')
-- insert a row into the dbo.v_persons view for a new person the attempt succeeds for insert into this view
INSERT INTO dbo.v_persons (BusinessEntityID, FirstName, LastName)
VALUES (30001, 'Iam', 'Analytics')
-- insert new record with BusinessEntityID = 30001 into dbo.employeedepthistory table through dbo.v_edh view
INSERT INTO dbo.v_edh (BusinessEntityID, DepartmentID, StartDate, EndDate)
VALUES (30001, 17, '2019-08-01', NULL)
----------------------------------------------------------------
-- add the first Sr. Data Scientist to the Analytics department insert a row into the dbo.employee_persons view for a new employee
INSERT INTO dbo.employee_persons (BusinessEntityID, OrganizationLevel, JobTitle)
VALUES (30002, 1, 'Sr. Data Scientist')
-- insert a row into the dbo.v_persons view for a new person the attempt succeeds for insert into this view
INSERT INTO dbo.v_persons (BusinessEntityID, FirstName, LastName)
VALUES (30002, 'Iam', 'Sr. Data Scientist')
-- insert new record with BusinessEntityID = 30001 into dbo.employeedepthistory table through dbo.v_edh view
INSERT INTO dbo.v_edh (BusinessEntityID, DepartmentID, StartDate, EndDate)
VALUES (30002, 17, '2019-08-05', NULL)
-- confirms insertion of two employees in the analytics department
SELECT * FROM dbo.employee_persons WHERE BusinessEntityID >= 30001
Here’s the results set from the last select statement in the preceding script. I hope this convinces you that this tip presents a short and sweet way to insert rows into tables from views – even if a display view has an is_updatable property value of No.
Next Steps
- You can run the scripts in this tip from any default database that you choose so long as your server has the AdventureWorks2014 database loaded. A reference to help you load this sample database is available in a prior MSSQLTips.com tip.
- A view is a virtual table. This tip demonstrates the use of views for updating tables via inserts and deletes. A prior tip demonstrates the use of views for easy access and re-use of data that depend on complex joins. The prior tip also includes code for populating the base tables referenced by the dbo.employee_persons view in the CodeModuleTypes database. Study either tip or both according to your needs.
