T-SQL Cross APPLY, MERGE

Write in front

Just looking at a stored procedure in the project, but also for a long time did not use SQL Server2008, a lot of writing and function feel unfamiliar, this encountered cross APPLY and merge syntax, the two have not touched at all before. So specifically checked the next SQL Server2008 combat.

1. Cross APPLY

From the tutorials and data query results, cross apply is entirely a syntactic sugar, the following is my query based on AdventrueWorkR2, using cross apply and inner join two ways.

Use [adventureworks2008r2]go/****** Object:  userdefinedfunction [dbo].[ Fn_workorderrouting]    Script date:08/13/2015 13:16:00 ******/set ansi_nulls ongoset quoted_identifier ONGOALTER FUNCTION [dbo]. [Fn_workorderrouting]  (@WorkOrderID int) RETURNS tableasreturn  SELECT A.workorderid,a.productid,a.operationsequence,a.locationid  from Production.workorderrouting a  WHERE [email protected]

　　

Select W.workorderid,w.orderqty,r.productid,r.operationsequence from Production.WorkOrder wCROSS APPLY dbo.fn_ Workorderrouting (W.workorderid) as Rorder by W.workorderid,w.orderqty,r.productid

　　

SELECT a.workorderid,a.orderqty,b.productid,b.operationsequence from Production.WorkOrder a INNER JOIN Production.workorderrouting B on a.workorderid= B.workorderidorder by A.workorderid,a.orderqty,b.productid

　

2. MERGE

The merge is simply for inserting, updating, deleting three operations, inserting, updating, and deleting the target table based on the result of the junction with the source table. The scenarios we use often are the master-slave table synchronization, or the synchronization and maintenance of the main table and the history table. The snippet below is one I saw on the Internet:

--Creating a temporary order Form create table orders (OrderID Int,customerid NCHAR (5)) go--add two lines of records to this table insert into Orders VALUES (1,n ' AAAAA ') INSERT into Orders VALUES (2,n ' bbbbb ') go--creates another table with the same schema by generating a table query, but only duplicates the first row of data past select * into Orders2 from Orders WHERE Ord Erid=1 go--updates the data for the second table update orders2 SET customerid=n ' ddddd '-merge two tables merge Orders o USING Orders2 O2 on O2. Orderid=o.orderid when matched and then UPDATE SET O.customerid=o2. customerid--if the match is reached, the target table is updated when the matched then INSERT VALUES (O2. Orderid,o2. CustomerID)--if the match is not reached, insert when the matched by source and then delete;--if the source table does not match, delete

　　

T-SQL Cross APPLY, MERGE