通过视图修改数据Modify Data Through a View

适用对象: 是SQL Server 是Azure SQL 数据库 是Azure Synapse Analytics (SQL DW) 是并行数据仓库 APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) yesParallel Data Warehouse

您可以使用 SQL ServerSQL ServerSQL Server Management StudioSQL Server Management StudioTransact-SQLTransact-SQL中修改基础基表的数据。You can modify the data of an underlying base table in SQL ServerSQL Server by using SQL Server Management StudioSQL Server Management Studio or Transact-SQLTransact-SQL.

开始之前Before You Begin

限制和局限Limitations and Restrictions

权限Permissions

需要对目标表的 UPDATE、INSERT 或 DELETE 权限(取决于执行的操作)。Requires UPDATE, INSERT, or DELETE permissions on the target table, depending on the action being performed.

使用 SQL Server Management StudioUsing SQL Server Management Studio

通过视图修改表数据To modify table data through a view

  1. “对象资源管理器” 中,展开包含视图的数据库,然后展开 “视图”In Object Explorer, expand the database that contains the view and then expand Views.

  2. 右键单击该视图,然后选择“编辑前 200 行” 。Right-click the view and select Edit Top 200 Rows.

  3. 可能需要在 SQL 窗格中修改 SELECT 语句以返回要修改的行。You may 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.

    重要说明!!IMPORTANT!! 如果视图引用多个基表,则不能删除行。You cannot 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.

    重要说明!IMPORTANT! 如果视图引用多个基表,则不能插入行。You cannot insert a row if the view references more than one base table.

使用 Transact-SQLUsing Transact-SQL

通过视图更新表数据To update table data through a view

  1. “对象资源管理器” 中,连接到 数据库引擎Database Engine的实例。In Object Explorer, connect to an instance of 数据库引擎Database Engine.

  2. 在标准菜单栏上,单击 “新建查询”On the Standard bar, click New Query.

  3. 将以下示例复制并粘贴到查询窗口中,然后单击“执行” 。Copy and paste the following example into the query window and click Execute. 此示例通过引用视图 StartDate 中的列为特定雇员更改 EndDateHumanResources.vEmployeeDepartmentHistory列中的值。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 AdventureWorks2012 ;   
    GO  
    UPDATE HumanResources.vEmployeeDepartmentHistory  
    SET StartDate = '20110203', EndDate = GETDATE()   
    WHERE LastName = N'Smith' AND FirstName = 'Samantha';   
    GO  
    

有关详细信息,请参阅 UPDATE (Transact-SQL)For more information, see UPDATE (Transact-SQL).

通过视图插入表数据To insert table data through a view

  1. “对象资源管理器” 中,连接到 数据库引擎Database Engine的实例。In Object Explorer, connect to an instance of 数据库引擎Database Engine.

  2. 在标准菜单栏上,单击 “新建查询”On the Standard bar, click New Query.

  3. 将以下示例复制并粘贴到查询窗口中,然后单击“执行” 。Copy and paste the following example into the query window and click Execute. 此示例通过指定视图 HumanResouces.Department 中的相关列,将一个新行插入到基表 HumanResources.vEmployeeDepartmentHistoryThe example inserts a new row into the base table HumanResouces.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 AdventureWorks2012 ;  
    GO  
    INSERT INTO HumanResources.vEmployeeDepartmentHistory (Department, GroupName)   
    VALUES ('MyDepartment', 'MyGroup');   
    GO  
    

有关详细信息,请参阅 INSERT (Transact-SQL)For more information, see INSERT (Transact-SQL).