Re: SQL - Can I use a CTE with MERGE ?

Hi all,
I'm not sure if this is allowed so I'm checking with the group.
I'm trying to use a common table expression (CTE) with a MERGE statement.

Glenn,

It looks like the table reference in the MERGE statement does not support common table expressions, although it appears from the documentation it should.

I tested using the following script:

Create Table
QTEMP.NEWTABLE As (
Select
Cast(0 As Integer) As ID,
Cast('' As Char(10)) As NAME
From
(Values 1) As A
) With No Data;

-- This doesn't work
-- Fails with SQL State 42601
Merge Into
QTEMP.NEWTABLE As OLD
Using
(With MYCTE As (
Select
Cast(1 As Integer) As ID,
Cast('JIM' As Char(10)) As NAME
From
(Values 1) As A)
Select
*
From
MYCTE) As NEW
On
(OLD.ID = NEW.ID)
When Not matched Then
Insert Values(NEW.ID, NEW.NAME)
When Matched Then
Update Set OLD.NAME = NEW.NAME;

-- This does work
Merge Into
QTEMP.NEWTABLE As OLD
Using
(
Select
Cast(1 As Integer) As ID,
Cast('JIM' As Char(10)) As NAME
From
(Values 1) As A) As NEW
On
(OLD.ID = NEW.ID)
When Not matched Then
Insert Values(NEW.ID, NEW.NAME)
When Matched Then
Update Set OLD.NAME = NEW.NAME;

Jim