Events
Mar 31, 11 PM - Apr 2, 11 PM
The biggest SQL, Fabric and Power BI learning event. March 31 – April 2. Use code FABINSIDER to save $400.
Register todayThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Applies to:
SQL Server
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
Analytics Platform System (PDW)
You can modify the data of an underlying base table in SQL Server by using SQL Server Management Studio or Transact-SQL.
Requires UPDATE, INSERT, or DELETE permissions on the target table, depending on the action being performed.
In Object Explorer, expand the database that contains the view and then expand Views.
Right-click the view and select Edit Top 200 Rows.
You may need to modify the SELECT statement in the SQL pane to return the rows to be modified.
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.
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.
In Object Explorer, connect to an instance of Database Engine.
On the Standard bar, click New Query.
Copy and paste the following example into the query window and click 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 (Transact-SQL).
In Object Explorer, connect to an instance of Database Engine.
On the Standard bar, click New Query.
Copy and paste the following example into the query window and click 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 (Transact-SQL).
Events
Mar 31, 11 PM - Apr 2, 11 PM
The biggest SQL, Fabric and Power BI learning event. March 31 – April 2. Use code FABINSIDER to save $400.
Register todayTraining