TRUNCATE TABLE, MERGE & OUTPUT Clause

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.

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, 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');Code language: PHP (php)

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);Code language: PHP (php)

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 NON 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 UPDATE statement, and 5 percent in the case of the INSERT statement.

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 (see Examples 7.25 and 7.26).

The OUTPUT clause uses the inserted and deleted tables 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_tableCode language: CSS (css)

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 the following chapter.) 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_tableCode language: CSS (css)

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_tableCode language: PHP (php)

The result is

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

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.

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 AdventureWorks.Production.Product
			WHERE ProductSubcategoryID IN ( SELECT ProductCategoryID
			FROM AdventureWorks.Production.ProductSubcategory
				WHERE ProductCategoryID IN ( SELECT ProductCategoryID
				FROM AdventureWorks.Production.ProductCategory
					WHERE Name = 'Bikes')));Code language: PHP (php)

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_Difference1;Code language: PHP (php)

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 AdventureWorks.dbo.temp_Difference;Code language: PHP (php)

The result is

TRUNCATE TABLE Statement

MERGE Statement

The OUTPUT Clause

Related Lessons: