Skip to content

modify-data-through-a-view.md

Latest commit

 

History

History
102 lines (67 loc) · 3.95 KB

modify-data-through-a-view.md

File metadata and controls

102 lines (67 loc) · 3.95 KB
title Modify Data Through a View
description Learn how to modify data through a view.
author WilliamDAssafMSFT
ms.author wiassaf
ms.reviewer randolphwest
ms.date 09/26/2025
ms.service sql
ms.subservice table-view-index
ms.topic how-to
ms.custom
ignite-2025
helpviewer_keywords
data modifications [SQL Server], views
views [SQL Server], modifying data through
modifying data [SQL Server], views
monikerRange >=aps-pdw-2016 || =azuresqldb-current || =azure-sqldw-latest || >=sql-server-2016 || >=sql-server-linux-2017 || =azuresqldb-mi-current || =fabric-sqldb

Modify data through a view

[!INCLUDE sql-asdb-asdbmi-asa-pdw-fabricsqldb]

You can modify the data of an underlying base table in SQL Server by using [!INCLUDE ssManStudioFull] or [!INCLUDE tsql].

Limitations

See the section 'Updatable Views' in CREATE VIEW.

Permissions

Requires UPDATE, INSERT, or DELETE permissions on the target table, depending on the action being performed.

Use SQL Server Management Studio

Modify table data through a view

  1. In Object Explorer, expand the database that contains the view and then expand Views.

  2. Right-click the view and select Edit Top 200 Rows.

  3. You might need to modify the SELECT statement in the SQL pane to return the rows to be modified.

  4. In the Results pane, locate the row to be changed or deleted. To delete the row, right-click the row and select Delete. To change data in one or more columns, modify the data in the column.

    You can't delete a row if the view references more than one base table. You can only update columns that belong to a single base table.

  5. To insert a row, scroll down to the end of the rows and insert the new values.

    You can't insert a row if the view references more than one base table.

Use Transact-SQL

Update table data through a view

  1. In Object Explorer, connect to an instance of [!INCLUDE ssDE].

  2. On the Standard bar, select New Query.

  3. Copy and paste the following example into the query window and select Execute. This example changes the value in the StartDate and EndDate columns for a specific employee by referencing columns in the view HumanResources.vEmployeeDepartmentHistory. This view returns values from two tables. This statement succeeds because the columns being modified are from only one of the base tables.

    USE AdventureWorks2022;
GO

UPDATE HumanResources.vEmployeeDepartmentHistory
    SET StartDate = '20110203',
        EndDate   = GETDATE()
WHERE LastName = N'Smith'
      AND FirstName = 'Samantha';
GO

For more information, see UPDATE.

Insert table data through a view

  1. In Object Explorer, connect to an instance of [!INCLUDE ssDE].

  2. On the Standard bar, select New Query.

  3. Copy and paste the following example into the query window and select Execute. The example inserts a new row into the base table HumanResources.Department by specifying the relevant columns from the view HumanResources.vEmployeeDepartmentHistory. The statement succeeds because only columns from a single base table are specified and the other columns in the base table have default values.

    USE AdventureWorks2022;
GO

INSERT INTO HumanResources.vEmployeeDepartmentHistory (Department, GroupName)
VALUES ('MyDepartment', 'MyGroup');
GO

For more information, see INSERT.

Related content