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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy