Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
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
bc160401693
mi
transaction in sql server
new problem SQL
SQL_RankingFunctions
calendar
BC160400549
Ggg
IOU2
M0413045.sql