| 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 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 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 Tag it:     |
| < Prev | Next > |
|---|
Comments
Quoting Christopher:
You can store the result set returned from the update command in table as shown below:
UPDATE Companies SET CompanyName = 'New name'
OUTPUT inserted.CompanyName, deleted.CompanyName, inserted.ID
into @table
WHERE ID = 3
See my example here:
Code:
<br />UPDATE test SET test='example test'<br />OUTPUT deleted.id, deleted.test INTO test2 (id, test2)<br />WHERE id<1022<br />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... ^^
thanks a bunch Stanislav Duben!
RSS feed for comments to this post.