OUTPUT command in MSSQL 2005

OUTPUT Command in MSSQL 2005In MSSQL 2005 is new T-SQL command OUTPUT, that was used only as output parameters in previous MSSQL version. Now you can use it in T-SQL commands to make data changes and see results by one command without using Triggers.

When you inserting new row, OUTPUT can returns you inserted record includes identity ID columns. For get ID value you must used Scope_Identity(), or get last value after insert. This is very easy now. 



Example:

INSERT INTO Companies (CompanyName, WebSite)
OUTPUT inserted.ID, inserted.CompanyName
VALUES ('Select SQL', 'www.select-sql.com')


This will return you:

ID          CompanyName
----------- ----------------------
3           Select SQL
(1 row(s) affected)



In update command you can use inserted or deleted. You can use both in one query. It shows you data after update (inserted table) and before update (deleted table).

Example:

UPDATE Companies SET CompanyName = 'New name'
OUTPUT inserted.CompanyName, deleted.CompanyName, inserted.ID
WHERE ID = 3


Query return:

NewCompanyName     OldCompanyName       ID
------------------ -------------------- -----------
New name           Select SQL           3
(1 row(s) affected)



Same way you can use OUTPUT in DELETE command, to show deleted record.

Example:

DELETE FROM Companies
OUTPUT deleted.CompanyName, deleted.ID
WHERE ID = 2


Returns:

CompanyName                     ID
------------------------------- -----------
Select SQL                      2
(1 row(s) affected)

Add comment

Security code
Refresh