Preview Inserts, Updates, Deletes, and Merges in SQL Server

April 16, 2014

Here is a quick and simple way to test the outcome of your changes before actually executing the change.

BEGIN TRANSACTION;

UPDATE
    DBname.dbo.Tablename
SET
    ColumName = 1

OUTPUT
    inserted.*

FROM
    DBname.dbo.Table1 T1
    JOIN DBname.dbo.Table2 T2 ON T1.ID = T2.ID1
    JOIN DBname.dbo.Table3 T3 ON T3.ID = T1.ID3
WHERE
    T3.Status = 1

ROLLBACK TRANSACTION;

The important parts are:

  1. BEGIN TRANSACTION – Starts the transaction.

  2. OUTPUT inserted.* – Outputs the result of the change to the results window.

  3. ROLLBACK TRANSACTION – Reverses the effects of the change, leaving the data in the same state it started in.


Profile picture

Hi, I'm Brock. I'm a Software Engineer at Microsoft. You should come learn with me. Oh, and you should also follow me on Twitter. 😊

Disclaimer: The opinions expressed herein are my own personal opinions and do not represent my employer’s view in any way.

© 2021, Brock Taylor. Built with Gatsby. Powered by Azure.