T-SQL – OUTPUT Option

Output option allows to get inserted, updated or deleted data from you query.
For example when you add row you can select identity value inserted when this row was created. You can also track (when update is done) which value was updated and how. You can also collect deleted data. Table in which you store changed data cannot have triggers and foreign keys.

Collect who and when is created as a new customer:

CREATE TABLE Sales.NewCustomers -- create table to save changed data
(
	newcustomer INT NOT NULL,
	timepoint DATETIME2 NOT NULL
);

INSERT INTO Sales.Customers (companyname, contactname, contacttitle, address, city, country, phone) --insert new customer
OUTPUT inserted.custid, GETDATE() INTO Sales.NewCustomers(newcustomer, timepoint) -- save also to NewCustomers table who (id from identity) and when was created as a new customer
VALUES ('My company', 'John Nowak', 'President', 'My Street', 'Milwaukee', 'USA', '030-3456788');

SELECT * FROM Sales.NewCustomers; -- check if new customer exists;

Collect how phone numbers are changed:

CREATE TABLE Sales.CustomersPhoneHistory -- create table to save changed data
(
	customerId INT NOT NULL,
	oldPhone nvarchar(24) NOT NULL, 
	newPhone nvarchar(24) NOT NULL
);

UPDATE Sales.Customers SET phone = '030-3456789' --change phone number
OUTPUT inserted.custid, deleted.phone, inserted.phone 
INTO Sales.CustomersPhoneHistory(customerId, oldPhone, newPhone) -- save previous and new phone number for updated customer
WHERE phone = '030-3456788';

SELECT * FROM Sales.CustomersPhoneHistory;

If you skip INTO part of the clause, values are put in the console not in the table. Every insert/update/delete clause can contain two OUTPUT options (one to table and one to console).

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