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).