Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
OUTPUT Clause transfers the records affected by the currently executed INSERT, UPDATE, or DELETE operations to the specified table. So we can easily capture newly created or updated or deleted records. The OUTPUT statement reads the newly inserted records from the INSERTED table, deleted or updated records from the DELETED table. These tables are the virtual tables that are created at that time.
Create the table which we will update and insert some records.
1 2 3 4 5 6 7 8 9 10 11 | CREATE TABLE [dbo].[Customer]( [CustomerId] [int] NULL, [City] [varchar](50) NULL, [Telephone] [varchar](50) NULL ) ON [PRIMARY] GO INSERT INTO [dbo].[Customer](CustomerId,City,Telephone) VALUES (1,'Ankara','05454077068'), (2,'İstanbul','03565626964'), (3,'İzmir','06531451255') SELECT * FROM [dbo].[Customer] |
Create the table which we will transfer the changes. Update the Telephone column where the City value is ‘İstanbul ‘ in the table and transfer the affected values to table which we have created named ChangesTable. Lastly, Select the ChangesTable.
1 2 3 4 5 6 7 8 | DECLARE @ChangesTable AS TABLE (CustomerId int, OldTelephone char(50),NewTelephone char(50)) UPDATE Customer SET Telephone='05459665478' OUTPUT INSERTED.CustomerId, DELETED.Telephone, INSERTED.Telephone INTO @ChangesTable WHERE City='İstanbul' SELECT * FROM @ChangesTable |
Another Example:
1 2 3 4 5 6 7 | DECLARE @DeletedTable AS TABLE (CustomerId int) DELETE Customer OUTPUT DELETED.CustomerId INTO @DeletedTable WHERE CustomerId=1 SELECT * FROM @DeletedTable |
