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.