Wednesday, April 16, 2008

OUTPUT clause in INSERT/UPDATE/DELETE statements

just playing with the new (for sql server 2005 and for me) OUTPUT clause.
I had some hopes for it but turns out i cant write something like


insert targettable (col1,col2)
output Inserted.col1, sourcetable.somecol
select somecol1,somecol2 from sourcetable



that is - i cant output values from the Inserted and also values from the source table :(
a bit disappointing in a way, of course, because i wanted to have backwards mapping between the source and inserted tables, but now I just decided to use one of the columns in the inserted table as this mapping ids placeholder

voila...

EDIT:
according to msdn article on T-SQL Delete
it is possible to OUTPUT exactly what I need...


DELETE Production.ProductProductPhoto
OUTPUT DELETED.ProductID,
p.Name,
p.ProductModelID,
DELETED.ProductPhotoID
INTO @MyTableVar
FROM Production.ProductProductPhoto AS ph
JOIN Production.Product as p
ON ph.ProductID = p.ProductID
WHERE p.ProductModelID BETWEEN 120 and 130;



strange...now I'll have to achieve the same results somehow...

1 comment :

Anonymous said...

I saw your question on scottgu's blog:
>>>
so..can we make changes to the source code of aspx page while debugging, or not yet?

i had the feeling this was craved by many developers...

it would be so much helpful when fixing bugs, even without live recompile & run

<<<<

In RTM version, this is supported, when enable tools->option->Debugging->Edit and Continue. If that's not what you looking for, maybe you can give us detailed feedback through connect?

Thanks!

Xinyang from MS Visual Web Development tools