af, Sql Server - rextester

af

--create sequence orderids as int
--minvalue 1
--cycle;

--select next value for orderids;
--select next value for orderids;

create table MyOrders(
orderid INT NOT NULL PRIMARY KEY,
custid INT NOT NULL,
empid INT NOT NULL,
orderdate DATE NOT NULL
)

DECLARE @Orders AS TABLE(
orderid INT NOT NULL PRIMARY KEY,
custid INT NOT NULL,
empid INT NOT NULL,
orderdate DATE NOT NULL,
shipcountry varchar(100)
);

INSERT INTO @Orders(orderid, custid, empid, orderdate,shipcountry) VALUES
(2, 1, 3, '20120612','Norway'),
(3, 2, 2, '20120612','Norway'),
(4, 3, 5, '20120612','India');

-- CTE for filtering rows
-- CTE for filtering rows
-- CTE for filtering rows
WITH SRC AS
(
SELECT *
FROM @Orders
WHERE shipcountry = N'Norway'
)

MERGE INTO MyOrders AS TGT
USING(select orderid, custid, empid, orderdate,shipcountry from SRC) SRC
ON SRC.orderid = TGT.orderid
WHEN MATCHED AND ( TGT.custid <> SRC.custid
OR TGT.empid <> SRC.empid
OR TGT.orderdate <> SRC.orderdate) THEN UPDATE
SET TGT.custid = SRC.custid,
TGT.empid = SRC.empid,
TGT.orderdate = SRC.orderdate
WHEN NOT MATCHED THEN INSERT
VALUES(SRC.orderid, SRC.custid, SRC.empid, SRC.orderdate);

select * from MyOrders


run \| edit \| history \| help	0

Please log in to post a comment.