Run Code
|
API
|
Code Wall
|
Users
|
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
Please
log in
to post a comment.
using lead() and sum() with over() in mssql
Data row generator (good performance, w/o recursive CTE)
find count of employees with salary more than their managers
customers
join
Turn column into string
SQL Server - NULL values with IN and NOT IN
Query through an error in Sql Server 2008 r2
Exam_2_Final
ESTEFANIA
Please log in to post a comment.