DBAs & Developers using everyday UPDATE statement, and everytime before update they want to see what to update, after this they want to see what has been updated.
OUTPUT clause can give us new value & old value in one way. According to the T-SQL syntax the OUTPUT clause can be used before condition statement WHERE, so let’s take a look with example:
--CREATE TEST TABLE CREATE TABLE TEST ( ID INT IDENTITY(1,1) NOT NULL, FNAME NVARCHAR(30), LNAME NVARCHAR(30), LOCATION NVARCHAR(40), CONSTRAINT pk_ID PRIMARY KEY (ID) ); --ADDING SOME TEST DATA INSERT INTO TEST SELECT 'A', 'A', 'PRISHTINA' UNION ALL SELECT 'B', 'B', 'PARIS' UNION ALL SELECT 'C', 'C', 'FLORIDA' UNION ALL SELECT 'D', 'D', 'LONDON' UNION ALL SELECT 'E', 'E', 'BERLIN' UNION ALL SELECT 'F', 'F', 'TIRANA'
Now we are going to use the Update statement and to see how we can retrieve results before and after:
--USING UPDATE UPDATE TEST SET FNAME = 'AAA' OUTPUT INSERTED.FNAME AS NEW_VALUE, DELETED.FNAME AS OLD_VALUE WHERE ID = 1
And the results will be:
As you can see we are available to see the two values, before and after, during update. OUTPUT clause can be used also during the INSERT & DELETE.
Let’s clean the table from database:
DROP TABLE TEST; GO
Have a nice SQLing…!