The Database Engine supports two additional modification statements:
- TRUNCATE TABLE
- MERGE
and the OUTPUT clause.
Both statements, together with the OUTPUT clause, will be explained in turn in the following subsections.
1. TRUNCATE TABLE Statement
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:
TRUNCATE TABLE table name
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;
CREATE TABLE bonus
(pr_no CHAR(4),
bonus SMALLINT DEFAULT 100);
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;
MERGE INTO bonus B
USING (SELECT project_no, budget
FROM project) E
ON (B.pr_no = E.project_no)
WHEN MATCHED THEN
UPDATE SET B.bonus = E.budget * 0.1
WHEN NOT MATCHED THEN
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
WHERE ProductID IN (SELECT ProductID
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));
GO
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()
WHEN NOT MATCHED BY SOURCE THEN
UPDATE SET 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.