MCQ King

MS SQL Server – TRUNCATE, MERGE & OUTPUT

Start MCQ

In Microsoft SQL Server, TRUNCATE, MERGE, and OUTPUT are SQL statements and operations that play key roles in managing and manipulating data within database tables.

  1. TRUNCATE:
    • The TRUNCATE TABLE statement is used to quickly remove all rows from a table, effectively deleting all data, but without the same level of logging as the DELETE statement.
    • It is faster than DELETE because it deallocates data pages rather than removing individual rows.
    • TRUNCATE doesn’t activate triggers and cannot be used if the table is referenced by a foreign key constraint.
    • It’s commonly used when you want to clear all data from a table without deleting the table structure itself.
    TRUNCATE TABLE TableName;
  2. MERGE:
    • The MERGE statement, also known as an “upsert,” is used for combining data from two tables, typically the source table (the one being merged) and the target table (the one being updated or inserted into).
    • MERGE allows you to specify conditions for updating existing rows, inserting new rows, or deleting rows from the target table in a single statement.
    • It’s often used for data synchronization between source and target tables.
    MERGE INTO TargetTable AS T USING SourceTable AS S ON T.PrimaryKey = S.PrimaryKey WHEN MATCHED THEN UPDATE SET T.Column1 = S.Column1, T.Column2 = S.Column2 WHEN NOT MATCHED BY TARGET THEN INSERT (Column1, Column2) VALUES (S.Column1, S.Column2) WHEN NOT MATCHED BY SOURCE THEN DELETE;
  3. OUTPUT:
    • The OUTPUT clause is used to return the results of data manipulation operations (such as INSERT, UPDATE, or DELETE) in a query result set.
    • It allows you to capture and view the affected rows after an operation is executed, which can be useful for auditing, logging, or further processing.
    • The OUTPUT clause can be combined with INSERT, UPDATE, and DELETE statements.
    • DELETE FROM SomeTable OUTPUT DELETED.ColumnName, DELETED.AnotherColumn WHERE SomeCondition; In this example, the OUTPUT clause returns the values of specific columns from the rows that were deleted.

These SQL Server operations (TRUNCATE, MERGE, and OUTPUT) provide powerful tools for managing data within your database, performing complex data transformations, and tracking changes made to your data. Understanding how to use these operations effectively can greatly enhance your ability to work with SQL Server databases.