SELECT SQL and upgrade your Performance - OUTPUT command in MSSQL 2005

OUTPUT command in MSSQL 2005

Written by Stanislav Duben

Wednesday, 29 August 2007

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

Quote this article on your site | Views: 2800 | Print

Comments (3)

1 Written by This e-mail address is being protected from spam bots, you need JavaScript enabled to view it , on 26-09-2007 04:24
i never know about this before, this a simple code but so informative

thanks a bunch Stanislav Duben!

2 Written by This e-mail address is being protected from spam bots, you need JavaScript enabled to view it , on 09-01-2008 00:00
got one question.. ^^

what if i want to store the
result returned by the "OUTPUT",
and not just sending it to client..

ex is i want the OUTPUT result to
be inserted to a variable table?..

if you know how, please let me know..
thanks.. peace out... ^^

3 Written by Stanislav Duben, on 10-01-2008 07:05
Hi Christopher, this is pretty simple. You can use syntax OUTPUT ... INTO ...

See my example here:

Code:

UPDATE test SET test='example test'
OUTPUT deleted.id, deleted.test INTO test2 (id, test2)
WHERE id<1022

Write Comment

Please keep the topic of messages relevant to the subject of the article.
Personal verbal attacks will be deleted.
Please don't use comments to plug your web site. Such material will be removed.
Just ensure to *Refresh* your browser for a new security code to be displayed prior to clicking on the 'Send' button.
Keep in mind that the above process only applies if you simply entered the wrong security code.

Name:

E-mail

Homepage

BBCode:

Comment:

MathGuard security question, please solve:


P 8T9 M7 W I 6CJ I 6P1 E2T Y C L 1X9 OBD X4U

Tag it:

< Prev		Next >

[ Back ]

Main Menu