How to capture changed data without schema modification : Part 1

Sometimes we need to capture changed row in table.  One good approach is to use change tracking option which is available in SQL Server 2008 and later. Another good solution is to use triggers on tables which linking deleted and inserted tables. But what if we have no permissions to modify database schema?

In that case we can use OUTPUT clause in INSERT OR UPDATE statement.

Here is an short example.

Let us create two table variables: @t_original  will be original (changed) table and @t_copy which will store deleted row.

Then we insert two rows into originl table. Changed rows will be inserted into table @t_copy

UPDATE  @t_original
SET     AnotherColumn = 4 ,
   Name = NULL
OUTPUT  DELETED.*
        INTO @t_copy
WHERE   id = 1 ;

Now we have both old and new rows and can use it later to compare and check differences.

SELECT  Copy.id ,
        Copy.NAME Name_Old ,
        Copy.AnotherColumn AnotherColumn_Old ,
        Original.NAME Name_New ,
        Original.AnotherColumn AnotherColumn_New
FROM    @t_copy Copy
        RIGHT OUTER JOIN @t_original Original ON Copy.id = Original.id

We will use results from those statements later to answer a question which columns were changed.