Viewing the results before and after, during Update

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:

NEW_VALUE    OLD_VALUE
AAA                     A

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…!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s