Something I didn’t know existed was the OUTPUT command in SQL Server:

Instead of just being told how many rows your SQL affected in a DELETE or UPDATE statement you can actually see what rows were affected. e.g.

DELETE
FROM your_table
OUTPUT deleted.*
WHERE your_column = ‘value_to_delete’;

UPDATE your_table
SET your_column = ‘new_value’
OUTPUT deleted.*, inserted.*
WHERE your_column = ‘old_value’;

The delete and update will still occur and you can specify columns instead of .* e.g. inserted.col1, inserted.col2

Just add in a ‘begin transaction’ and ‘commit’ or ‘rollback’ and you can test out your UPDATE and DELETE statements before you do them.

SQL Server OUTPUT Command
Tagged on:         

Leave a Reply

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

+ fifty three = 59