How to insert a row in a view in SQL Server

In this SQL Server tutorial, we will learn how to insert a row in a view in SQL Server. Additionally, we will also cover the following set of topics.

• How to insert a row in a view in SQL Server
• How to insert a row in a view in SQL Server Management Studio
• Permissions required to insert a row in a view in SQL Server
• Restrictions on inserting data through views in SQL Server
• Can we insert data into view in SQL Server
• SQL Server insert into view multiple tables
• Which view does not always allow DML operations through the view
• How can we insert data into a view in SQL Server

Can we insert data into view in SQL Server

In SQL Server, a VIEW is just like a virtual table that holds data from one or more than one table. A view includes a set of SQL queries for retrieving data from the database. And it does not even exist in the database physically.

So, Yes, we can insert data into view in SQL Server. But, remember that the actual data will be inserted into the underlying table, and a view will just return the data of that underlying table. Because a view in SQL Server is not a physical table and does not hold any data.

Still, in SQL Server, we can create an updatable view that is used to update the data of an underlying table. And this updation includes inserting rows through a view in SQL Server.

Read: How to get inserted value in trigger SQL Server

Restrictions on inserting data through views in SQL Server

In the previous section, we get to know that we can insert data into view in SQL Server. But, for this implementation, there are some restrictions while creating a view in SQL Server. And these restrictions are as follows.

• While inserting data using INSERT statement must point to columns from only one underlying table.
• We should ignore using aggregate functions like MAX(), AVG(), etc in a view. Moreover, we should also not make any computation using JOIN, UNION, etc.
• GROUP BY, HAVING, and DISTINCT clauses should no impact the selected columns om a view.
• We should utilize the TOP option and WITH CHECK OPTION clause together anywhere in the SELECT statement.

Also, check: How to delete a view in SQL Server

Permissions required to insert a row in a view in SQL Server

Other than the restrictions on creating a view, a user role needs to have some permissions to insert data in a SQL Server View.

So, for this implementation, a user needs to have INSERT permission on the underlying table. Furthermore, let’s understand how to grant INSERT permission to a user in SQL Server.

USE database

GRANT INSERT ON OBJECT::schema.table_name TO user_name;
GO

Let’s understand the syntax using an example in SQL Server.

USE [sqlserverguides];

GRANT INSERT ON OBJECT::dbo.CustomerTable TO my_user;
GO

In the above example, we are granting the INSERT permission on the CustomerTable object to a user whose name is my_user.

Read: How to see view definition in SQL Server

How to insert a row in a view in SQL Server

In this section, we will discuss how to insert a row in a view in SQL Server using an example. So, once a user has the required permissions, the first step is to create a view in the database while considering all the restrictions.

For the example illustration, let’s, create a view in SQL Server using the following query.

USE [sqlserverguides]
GO

CREATE VIEW [dbo].[USCustomerView]
AS
SELECT id, customer_name, city, country
FROM CustomerTable
WHERE country = 'United States'

GO

In the above example, we have created a view in the sqlserverguides database with the name  USCustomerView. And this view will return the customer name, id, city, and country from the CustomerTable table where the country is the United States.

And if we query the view, it will return all the records from the CustomerTable where the country is the United States.

Now that we have created a view in SQL Server, we will use this view and insert one row in the underlying table. The example for this task is shown below.

USE [sqlserverguides]
GO

INSERT INTO [dbo].[USCustomerView](id, customer_name, city, country)
VALUES(11, 'Jarrad Richings', 'Los Angeles', 'United States')
GO

After executing the above query, the values will be inserted into the CustomerTable. And now if we query the view, it will return the updated data from the table.

Read: SQL Server view order by

How to insert a row in a view in SQL Server Management Studio

Other than using a Transact-SQL query, we can also use SQL Server Management Studio to insert a row in a view. For this task, we can follow the following given steps.

• First, run SQL Server Management Studio and connect to the required database instance.
• Next, from the Object Explorer, first, expand the Database dirctory and then expand the required Databases.
• Unders the required Database, expand the Views directory.
• Under the Views directory, right-click the required view and click on Edit TOP 200 Rows option. This will open a tabular grid page in the SQL Server Management Studio.

• Now, on the tabular grid, we can insert new rows. For this, just move to the last empty cell and enter the records there.

Note: We can only insert data in a view that refers to only one underlying table.

Read: Create a table from view in SQL Server

SQL Server insert into view multiple tables

In SQL Server, to insert some data into a view, the view should refer to only one underlying table. And if try to insert some data into a view referring to multiple tabes, then SQL Server will return an error.

Let’s understand this implementation using an example. And for this, we will create a view using the following query.

USE [sqlserverguides]
GO

CREATE VIEW [dbo].[USCityView]
AS
SELECT City, Country FROM dbo.City_1
WHERE Country = 'United States'
UNION
SELECT City, Country FROM dbo.City_2
WHERE Country = 'United States';
GO

In the above query, we have created a view with the name USCityView. And in the definition, we are using 2 SELECT statements to fetch the city and country columns from respective tables. So, in this view, we are using 2 different tables. Here is the sample output of the above view.

Now, let’s use this view to insert some records in the underlying tables.

Also, check: Disable Trigger in SQL Server

Alterative Method

To insert data through view in multiple tables, we need to use the INSTEAD OF TRIGGER in SQL Server.

An INSTEAD OF TRIGGER in SQL Server allows executing other statements specified in the trigger instead of an INSERT, DELETE, or UPDATE statement to a table or view. While the insert, delete, or update operation is not performed at all.

First, let’s understand how to create and use the INSTEAD OF TRIGGER in SQL Server. For this, let’s, take a look at the syntax of creating it.

CREATE TRIGGER trigger_name
ON view_name
INSTEAD OF {[INSERT] [,] [UPDATE] [,] [DELETE] }
AS
sql_statements

• In the above syntax, first, we are using the CREATE TRIGGER statement to create a trigger with a specified name.
• After this, we have to specify the associated view name.
• Next, we have to use the INSTEAD OF statement to specify the event for the trigger.
• In the body, we can specify one or more SQL statements.

Now, let’s, use the above syntax and create an INSTEAD OF TRIGGER for our USCityView view. For this, consider the following SQL code.

USE [sqlserverguides]
GO

CREATE TRIGGER Trg_Vw_Cities 
ON USCityView
INSTEAD OF INSERT
AS
BEGIN
    INSERT INTO dbo.City_1(city, country)
    SELECT i.City, i.Country
    FROM inserted i;

    INSERT INTO dbo.City_2(city, country)
    SELECT i.City, i.Country
    FROM inserted i;
END
GO

In the above query, we have created a trigger that will insert values into the City_1 and City_2 tables. And the INSERT statement on view will not be executed instead trigger queries will get executed.

Again, let’s, use the INSERT statement with the view to add a record.

USE [sqlserverguides]
GO

INSERT INTO [dbo].[USCityView](City, Country)
VALUES ('Chicago', 'United States')
GO

Now, if query the view, we can observe the newly inserted row from the result.

Read: View SQL Server Error Logs

Which view does not always allow DML operations through the view

In SQL Server, complex views do not always allow DML operations through the view.

A complex view in SQL Server is the one that holds data from more than one table. However, a complex view might use group functions like MAX(), COUNT(), etc. And it can also be using join conditions and clauses like GROUP BY, DISTINCT, etc.

In SQL Server, a simple view always allows DML operations through the view. A simple view in SQL Server is the one that holds data from only one table. While it also doesn’t use group functions like MAX(), COUNT(), etc.

You may also like to read the following SQL Server tutorials.

• SQL Server logical operators
• Alter view in SQL Server
• SQL Server Datetime functions
• Comparison Operators in SQL Server
• Arithmetic operators in SQL Server
• Stored procedure in SQL Server

So, in this tutorial, we have learned how to insert a row in a view in SQL Server. Additionally, we have also covered the following set of topics.

• How to insert a row in a view in SQL Server
• How to insert a row in a view in SQL Server Management Studio
• Permissions required to insert a row in a view in SQL Server
• Restrictions on inserting data through views in SQL Server
• Can we insert data into view in SQL Server
• SQL Server insert into view multiple tables
• Which view does not always allow DML operations through the view
• How can we insert data into a view in SQL Server