March 31, 2008 at 7:15 am
im trying the following:
declare @OrderDetails as xml
set @OrderDetails = ' '
select @OrderDetails
--Merge Quer
;WITH OrderInfo AS (
SELECT
x.h.value('@OrderNumber', 'VARCHAR(20)') AS OrderNumber,
x.h.value('@CustomerNumber', 'VARCHAR(20)') AS CustomerNumber,
x.h.value('@OrderDate', 'VARCHAR(20)') AS OrderDate
FROM @OrderDetails.nodes('/OrderInfo/OrderHeader') AS x(h)
)
MERGE OrderHeader AS h
USING OrderInfo AS o
ON (h.OrderNumber = o.OrderNumber)
WHEN MATCHED THEN
UPDATE SET h.LastModifiedDate = o.OrderDate
WHEN NOT MATCHED THEN
INSERT
select o.ordernumber,o.orderdate,'ACC',c.customerid,o.customernumber,o.orderdate
from orderinfo as o join customer as c on o.customernumber = c.customernumber
;
the problem im having the issue with is this bit:
WHEN NOT MATCHED THEN
INSERT
select o.ordernumber,o.orderdate,'ACC',c.customerid,o.customernumber,o.orderdate
from orderinfo as o join customer as c on o.customernumber = c.customernumber
;
I cant insert using a select statement. managment studio only tells me that it expects a DEFAULT or VALUES keywore instead of select. I have tried numerous ways of phrasing the select and insert part but to no avail. Is there anyway to use select here?
March 31, 2008 at 7:34 am
The notmatched rows come from your initial CTE (or SELECT) from what I understand.
My understanding was that you write a SELECT, which is the input into MERGE. The MERGE then decides for each row of your select, if it's a matched or not matched row and uses that to do the insert/update/delete.
March 31, 2008 at 7:56 am
you are absolutely correct.
Ive been staring at the screen for so long ive forgotten the point of the Merge command!
Ive gotten it to work this way:
;WITH OrderInfo AS (
SELECT
oi.ordernumber,oi.customernumber,oi.orderdate,c.customerid
from @OrderTable as oi join customer as c on oi.customernumber = c.customernumber
)
MERGE OrderHeader AS h
USING OrderInfo AS o
ON (h.OrderNumber = o.OrderNumber)
WHEN MATCHED THEN
UPDATE SET h.LastModifiedDate = o.OrderDate
WHEN NOT MATCHED THEN
INSERT
values(o.ordernumber,o.orderdate,'ACC',o.customerid,o.customernumber,o.orderdate)
thanks for getting back to me on this so quickly!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply