Re: SQL2011 and writeable CTE

2010/11/8 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:
> 2010/11/8 Hitoshi Harada <umi(dot)tanuki(at)gmail(dot)com>:

>> Hmm, this looks very different from our writeable CTEs. And I can see
>> many issues like syntax ambiguity and execution order if we support
>> it. AFAIK the most significant reason why we are working on CTEs is
>> that CTEs are regarded as something like materialized table.
>>
>> So I think we are going on writeable "CTEs" unless there are no
>> objection rather than pursuing the standard.
>> Thanks for sharing anyway.
>>
>
> I found, so writeable CTE was implemented in Microsoft SQL server too.
> Can be our implementation compatible?
>

I don't believe MS SQL Server doesn't implement such our writeable
CTEs. If you mention about this link
(http://msdn.microsoft.com/en-us/library/ms175972.aspx), it says:

[ WITH <common_table_expression> [ ,...n ] ]

<common_table_expression>::=
expression_name [ (column_name [ ,...n ] ) ]
AS
(CTE_query_definition)

and

CTE_query_definition

Specifies a SELECT statement whose result set populates the common
table expression. <snip>

It says about top-level DML under CTE shown below which we already
have in our HEAD.

WITH t AS (SELECT * FROM src) INSERT INTO dest SELECT * FROM t;

as "A CTE must be followed by a single SELECT, INSERT, UPDATE, MERGE,
or DELETE statement that references some or all the CTE columns. A CTE
can also be specified in a CREATE VIEW statement as part of the
defining SELECT statement of the view."

They are different.

Regards,

--
Hitoshi Harada