Modification of a Table’s Contents in SQL Server: Other T-SQL Modification Statements and Clauses

The Database Engine supports two additional modification statements:


and the OUTPUT clause.

Both statements, together with the OUTPUT clause, will be explained in turn in the following subsections.


The Transact-SQL language also supports the TRUNCATE TABLE statement. This statement normally provides a “faster executing” version of the DELETE statement without the WHERE clause. The TRUNCATE TABLE statement deletes all rows from a table more quickly than does the DELETE statement because it drops the contents of the table page by page, while DELETE drops the contents row by row.

NOTE The TRUNCATE TABLE statement is a Transact-SQL extension to the SQL standard.

The TRUNCATE TABLE statement has the following form:


TIP If you want to delete all rows from a table, use the TRUNCATE TABLE statement. This statement is significantly faster than DELETE because it is minimally logged and there are just a few entries in the log during its execution. (Logging is discussed in detail in Chapter 13.)

2. MERGE Statement

The MERGE statement combines the sequence of conditional INSERT, UPDATE, and DELETE statements in a single atomic statement, depending on the existence of a record. In other words, you can sync two different tables so that the content of the target table is modified based on differences found in the source table.

The main application area for MERGE is a data warehouse environment (see Chapter 24), where tables need to be refreshed periodically with new data arriving from online transaction processing (OLTP) systems. This new data may contain changes to existing rows in tables and/ or new rows that need to be inserted. If a row in the new data corresponds to an item that already exists in the table, an UPDATE or a DELETE statement is performed. Otherwise, an INSERT statement is performed.

The alternative way, which you can use instead of applying the MERGE statement, is to write a sequence of INSERT, UPDATE, and DELETE statements, where, for each row, the decision is made whether to insert, delete, or update the data. This old approach has significant performance disadvantages: it requires multiple data scans and operates on a record-by-record basis.

Examples 7.20 and 7.21 show the use of the MERGE statement.

Example 7.20

USE sample;


(pr_no CHAR(4),


INSERT INTO bonus (pr_no) VALUES (‘p1’);

Example 7.20 creates the bonus table, which contains one row, (p1, 100). This table will be used for merging.

Example 7.21

USE sample;


USING (SELECT project_no, budget

FROM project) E

ON (B.pr_no = E.project_no)


UPDATE SET B.bonus = E.budget * 0.1


INSERT (pr_no, bonus)

VALUES (E.project_no, E.budget * 0.05);

The MERGE statement in Example 7.21 modifies the data in the bonus table depending on the existing values in the pr_no column. If a value from the project_no column of the project table appears in the pr_no column of the bonus table, the MATCHED branch will be executed and the existing value will be updated. Otherwise, the NOT MATCHED branch will be executed and the corresponding INSERT statement will insert new rows in the bonus table.

The content of the bonus table after the execution of the MERGE statement is as follows:

From the result set, you can see that a value of the bonus column represents 10 percent of the original value in the case of the first row, which is updated, and 5 percent in the case of the second and third rows, which are inserted.

3. The OUTPUT Clause

The result of the execution of an INSERT, UPDATE, or DELETE statement contains by default only the text concerning the number of modified rows (“3 rows deleted” for instance). If the content of such a result doesn’t fit your needs, you can use the OUTPUT clause, which displays explicitly the rows that are inserted or updated in the table or deleted from it.

NOTE The OUTPUT clause is also part of the MERGE statement. It returns an output for each modified row in the target table (as will be demonstrated in Examples 7.25 and 7.26).

The OUTPUT clause uses the inserted and deleted tables (explained in Chapter 14) to display the corresponding result. Also, the OUTPUT clause must be used with an INTO expression to fill a table. For this reason, you use a table variable to store the result.

Example 7.22 shows how the OUTPUT statement works with a DELETE statement.

Example 7.22

USE sample;

DECLARE @del_table TABLE (emp_no INT, emp_lname CHAR(20));

DELETE employee

OUTPUT DELETED.emp_no, DELETED.emp_lname INTO @del_table WHERE emp_no > 15000;

SELECT * FROM @del_table;

If the content of the employee table is in the initial state, the execution of the statements in Example 7.22 produces the following result:

First, Example 7.22 declares the table variable @del_table with two columns: emp_no and emp_lname. (Variables are explained in detail in Chapter 8.) This table will be used to store the deleted rows. The syntax of the DELETE statement is enhanced with the OUTPUT option:

OUTPUT DELETED.emp_no, DELETED.emp_lname INTO @del_table

Using this option, the system stores the deleted rows in the deleted table, which is then copied in the @del table variable.

Example 7.23 shows the use of the OUTPUT option in an UPDATE statement.

Example 7.23

USE sample;

DECLARE @update_table TABLE

(emp_no INT, project_no CHAR(20),old_job CHAR(20),new_job CHAR(20));

UPDATE works_on

SET job = NULL

OUTPUT DELETED.emp_no, DELETED.project_no,

DELETED.job, INSERTED.job INTO @update_table

WHERE job = ‘Clerk’;

SELECT * FROM @update_table;

The result is

The following examples show the use of the OUTPUT clause within the MERGE statement.

NOTE The use of the OUTPUT clause within the MERGE statement is complex. Therefore, you might want to skip the rest of this section on the first reading of the book and make a note to return to it later.

Suppose that your marketing department decides to give customers a price reduction of 20 percent for all bikes that cost more than $500. The SELECT statement in Example 7.24 selects all products that cost more than $500 and inserts them in the temp_PriceList temporary table. The consecutive UPDATE statement searches for all bikes and reduces their price. (The UPDATE statement uses three subqueries to get the necessary information from three tables: Product, Production.ProductSubcategory, and Production .ProductCategory.)

Example 7.24

USE AdventureWorks;

SELECT ProductID, Product.Name as ProductName, ListPrice

INTO temp_PriceList

FROM Production.Product

WHERE ListPrice > 500;

UPDATE temp_PriceList

SET ListPrice = ListPrice * 0.8


FROM Production.Product

WHERE ProductSubcategoryID IN ( SELECT ProductCategoryID

FROM Production.ProductSubcategory

WHERE ProductCategoryID IN ( SELECT ProductCategoryID

FROM Production.ProductCategory

WHERE Name = ‘Bikes’)));

The CREATE TABLE statement in Example 7.25 creates a new table, temp_Difference, that will be used to store the result set of the MERGE statement. After that, the MERGE statement compares the complete list of the products with the new list (given in the temp_priceList table) and inserts the modified prices for all bicycles by using the UPDATE SET clause. (Besides the insertion of the new prices for all bicycles, the statement also changes the ModifiedDate column for all products and sets it to the current date.) The OUTPUT clause in Example 7.25 writes the old and new prices in the temporary table called temp_Difference. That way, you can later calculate the aggregate differences, if needed.

Example 7.25

USE AdventureWorks;

CREATE TABLE temp_Difference

(old DEC (10,2), new DEC(10,2));


MERGE INTO Production. Product

USING temp_PriceList ON Product.ProductID = temp_PriceList.ProductID

WHEN MATCHED AND Product.ListPrice <> temp_PriceList.ListPrice

THEN UPDATE SET ListPrice = temp_PriceList.ListPrice, ModifiedDate = GETDATE()



OUTPUT DELETED.ListPrice, INSERTED.ListPrice INTO temp_Difference;

Example 7.26 shows the computation of the overall difference, the result of the preceding modifications.

Example 7.26

USE AdventureWorks;

SELECT SUM(old) – SUM(new) AS diff

FROM dbo.temp_Difference;

The result is

Source: Petkovic Dusan (2020), Microsoft SQL Server 2019: A Beginner’s Guide, Seventh Edition-McGraw-Hill Education.

Leave a Reply

Your email address will not be published. Required fields are marked *