OUTPUT

Keyword OUTPUT

The OUTPUT clause returns information from, or expressions based on, each row affected by an INSERT, UPDATE, DELETE, or MERGE statement. These results can be returned to the processing application.

Syntax:

    <DML statement> OUTPUT <output_list>

where <output_list> is a comma separated list of:

• INSERTED.* - All columns from the inserted or updated row
• DELETED.* - All columns from the deleted or updated row
• INSERTED.column_name - Specific column from the inserted or updated row
• DELETED.column_name - Specific column from the deleted or updated row
• expression - Any valid expression using the above columns

INSERT with OUTPUT

Return the inserted values, which is useful for retrieving auto-generated keys or default values.

    // Return all inserted columns
    var res = alasql('INSERT INTO users VALUES (1, "John", 30) OUTPUT INSERTED.*');
    
    // Return specific columns
    var res = alasql('INSERT INTO products VALUES (1, "Widget") OUTPUT INSERTED.id, INSERTED.name');

DELETE with OUTPUT

Return the rows that were deleted.

    // Return all columns of deleted rows
    var res = alasql('DELETE FROM orders WHERE amount > 100 OUTPUT DELETED.*');
    
    // Return specific columns
    var res = alasql('DELETE FROM inventory WHERE qty = 0 OUTPUT DELETED.id, DELETED.item');

UPDATE with OUTPUT

Return both the old (DELETED) and new (INSERTED) values of updated rows.

    // Return new values
    var res = alasql('UPDATE employees SET salary = salary * 1.1 WHERE id = 1 OUTPUT INSERTED.*');
    
    // Return old values
    var res = alasql('UPDATE stock SET price = 160 WHERE symbol = "AAPL" OUTPUT DELETED.price');
    
    // Return both old and new values with aliases
    var res = alasql('UPDATE prices SET price = 15 WHERE id = 1 OUTPUT DELETED.price AS OldPrice, INSERTED.price AS NewPrice');

Notes

• The OUTPUT clause is supported in INSERT, UPDATE, DELETE, MERGE statements.
• INSERTED table contains the new rows being inserted or updated.
• DELETED table contains the old rows being deleted or updated.
• For INSERT, only INSERTED is available.
• For DELETE, only DELETED is available.
• For UPDATE, both INSERTED and DELETED are available.

See also: INSERT, UPDATE, DELETE