INSERT...SELECT and OUTPUT question

Why would this syntax be valid and the second one is not (below)?
begin tran
USE AdventureWorks
GO
DECLARE @.MyTableVar table (
ProductID int NOT NULL,
ProductName nvarchar(50)NOT NULL,
ProductModelID int NOT NULL,
PhotoID int NOT NULL);
DELETE Production.ProductProductPhoto
OUTPUT DELETED.ProductID,
p.Name,
p.ProductModelID,
DELETED.ProductPhotoID
INTO @.MyTableVar
--OUTPUT DELETED.ProductID, DELETED.ProductPhotoID, GETDATE() AS DeletedDate
FROM Production.ProductProductPhoto AS ph
JOIN Production.Product as p
ON ph.ProductID = p.ProductID
WHERE p.ProductID BETWEEN 800 and 810;
--Display the results of the table variable.
SELECT ProductID, ProductName, PhotoID, ProductModelID
FROM @.MyTableVar;
GO
rollback
This is not valid. Why not? Am I missing something?
Is it that only with UPDATE/DELETE other fields from JOIN can be in output ?
USE AdventureWorks ;
GO
IF OBJECT_ID ('dbo.EmployeeSales', 'U') IS NOT NULL
DROP TABLE dbo.EmployeeSales;
GO
CREATE TABLE dbo.EmployeeSales
( EmployeeID nvarchar(11) NOT NULL,
LastName nvarchar(20) NOT NULL,
FirstName nvarchar(20) NOT NULL,
CurrentSales money NOT NULL,
ProjectedSales money NOT NULL
);
GO
INSERT INTO dbo.EmployeeSales
OUTPUT INSERTED.EmployeeID,
INSERTED.LastName,
INSERTED.FirstName,
INSERTED.CurrentSales,
e.EmployeeID
SELECT e.EmployeeID, c.LastName, c.FirstName, sp.SalesYTD, sp.SalesYTD * 1.1
0
FROM HumanResources.Employee AS e
INNER JOIN Sales.SalesPerson AS sp
ON e.EmployeeID = sp.SalesPersonID
INNER JOIN Person.Contact AS c
ON e.ContactID = c.ContactID
WHERE e.EmployeeID LIKE '2%'
ORDER BY c.LastName, c.FirstName;
GO
SELECT EmployeeID, LastName, FirstName, CurrentSales, ProjectedSales
FROM dbo.EmployeeSales;
GOFarmer (someone@.somewhere.com) writes:
> Why would this syntax be valid and the second one is not (below)?
> 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.ProductID BETWEEN 800 and 810;
>...
> INSERT INTO dbo.EmployeeSales
> OUTPUT INSERTED.EmployeeID,
> INSERTED.LastName,
> INSERTED.FirstName,
> INSERTED.CurrentSales,
> e.EmployeeID
> SELECT e.EmployeeID, c.LastName, c.FirstName, sp.SalesYTD,
> sp.SalesYTD * 1.10
> FROM HumanResources.Employee AS e
>...
The syntax diagram in Books Online gives us:
<column_name> ::=
{ DELETED | INSERTED | from_table_name } . { * | column_name }
In the comments section we find:
from_table_name
Is a column prefix that specifies a table included in the FROM clause
of a DELETE or UPDATE statement that is used to specify the rows to
update or delete.
Thus, Books Online clearly says that you cannot use e.EmployeeID in the
OUTPUT clause of an INSERT statement.
Then remains the question why it is so. We look at the syntax diagram
for INSERT:
[ WITH <common_table_expression> [ ,...n ] ]
INSERT
[ TOP ( expression ) [ PERCENT ] ]
[ INTO]
{ <object> | rowset_function_limited
[ WITH ( <Table_Hint_Limited> [ ...n ] ) ]
}
{
[ ( column_list ) ]
[ <OUTPUT Clause> ]
{ VALUES ( { DEFAULT | NULL | expression } [ ,...n ] )
| derived_table
| execute_statement
}
}
| DEFAULT VALUES
[; ]
Note here that the SELECT statement appears in this grammar as a
derived table. A derived table has the property, that it does not
see things outside of if, and the outside cannot look in.
In looser terms, we can simply say that the OUTPUT clause is part of
the INSERT clause in a way that the SELECT statement is not, and thus
does not have visibility of what is in the SELECT statement.
For DELETE or UPDATE it's a different matter as the FROM clause are
part of the DELETE and UPDATE statments themselves.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thank you,
very good explanation on your part. I see it now. I should have read more
carefully.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns976FD66F91C3BYazorman@.127.0.0.1...
> Farmer (someone@.somewhere.com) writes:
> The syntax diagram in Books Online gives us:
> <column_name> ::=
> { DELETED | INSERTED | from_table_name } . { * | column_name }
> In the comments section we find:
> from_table_name
> Is a column prefix that specifies a table included in the FROM clause
> of a DELETE or UPDATE statement that is used to specify the rows to
> update or delete.
> Thus, Books Online clearly says that you cannot use e.EmployeeID in the
> OUTPUT clause of an INSERT statement.
> Then remains the question why it is so. We look at the syntax diagram
> for INSERT:
> [ WITH <common_table_expression> [ ,...n ] ]
> INSERT
> [ TOP ( expression ) [ PERCENT ] ]
> [ INTO]
> { <object> | rowset_function_limited
> [ WITH ( <Table_Hint_Limited> [ ...n ] ) ]
> }
> {
> [ ( column_list ) ]
> [ <OUTPUT Clause> ]
> { VALUES ( { DEFAULT | NULL | expression } [ ,...n ] )
> | derived_table
> | execute_statement
> }
> }
> | DEFAULT VALUES
> [; ]
> Note here that the SELECT statement appears in this grammar as a
> derived table. A derived table has the property, that it does not
> see things outside of if, and the outside cannot look in.
> In looser terms, we can simply say that the OUTPUT clause is part of
> the INSERT clause in a way that the SELECT statement is not, and thus
> does not have visibility of what is in the SELECT statement.
> For DELETE or UPDATE it's a different matter as the FROM clause are
> part of the DELETE and UPDATE statments themselves.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Farmer, try this one
IF OBJECT_ID ('dbo.EmployeeSales', 'U') IS NOT NULL
DROP TABLE dbo.EmployeeSales;
GO
CREATE TABLE dbo.EmployeeSales
( EmployeeID nvarchar(11) NOT NULL,
LastName nvarchar(20) NOT NULL
);
GO
CREATE TABLE #Temp ( EmployeeID int not null,
LastName nvarchar(20) NOT NULL)-
INSERT INTO dbo.EmployeeSales(EmployeeID,LastName)
OUTPUT INSERTED.EmployeeID, INSERTED.LastName INTO #Temp
SELECT e.EmployeeID, c.LastName
FROM HumanResources.Employee AS e
INNER JOIN Sales.SalesPerson AS sp
ON e.EmployeeID = sp.SalesPersonID
INNER JOIN Person.Contact AS c
ON e.ContactID = c.ContactID
WHERE e.EmployeeID LIKE '2%'
ORDER BY c.LastName, c.FirstName;
select * from #Temp
go
"Farmer" <someone@.somewhere.com> wrote in message
news:%23AWUfJbNGHA.3164@.TK2MSFTNGP11.phx.gbl...
> Thank you,
> very good explanation on your part. I see it now. I should have read more
> carefully.
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns976FD66F91C3BYazorman@.127.0.0.1...
>|||Thanks
You have missed my point though. This does not work and this can be a field
from a JOIN table from FROM statement.
OUTPUT e.EmployeeID, INSERTED.LastName INTO #Temp
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:O$1jqNeNGHA.3936@.TK2MSFTNGP12.phx.gbl
..
> Farmer, try this one
>
> IF OBJECT_ID ('dbo.EmployeeSales', 'U') IS NOT NULL
>
> DROP TABLE dbo.EmployeeSales;
>
> GO
>
> CREATE TABLE dbo.EmployeeSales
>
> ( EmployeeID nvarchar(11) NOT NULL,
>
> LastName nvarchar(20) NOT NULL
>
> );
>
> GO
>
> CREATE TABLE #Temp ( EmployeeID int not null,
>
> LastName nvarchar(20) NOT NULL)-
>
> INSERT INTO dbo.EmployeeSales(EmployeeID,LastName)
>
> OUTPUT INSERTED.EmployeeID, INSERTED.LastName INTO #Temp
>
> SELECT e.EmployeeID, c.LastName
>
> FROM HumanResources.Employee AS e
>
> INNER JOIN Sales.SalesPerson AS sp
>
> ON e.EmployeeID = sp.SalesPersonID
>
> INNER JOIN Person.Contact AS c
>
> ON e.ContactID = c.ContactID
>
> WHERE e.EmployeeID LIKE '2%'
>
> ORDER BY c.LastName, c.FirstName;
>
>
>
> select * from #Temp
>
> go
>
> "Farmer" <someone@.somewhere.com> wrote in message
> news:%23AWUfJbNGHA.3164@.TK2MSFTNGP11.phx.gbl...
>
>