Merge operation in SQL Server (reprint)

Today in a stored procedure see merge this keyword, the first idea is that this is the concept of configuration management, the next two times the changes are merged together. Later on the TechNet search Discovery Shia, originally is another SQL keyword, T-SQL syntax is still quite rich. This is a study note, there is nothing new, here is a link to the address on TechNet for everyone to refer to the authority: http://technet.microsoft.com/zh-cn/library/ Bb510625.aspx, here the specific grammar does not go to the depth, just a few examples of the actual operation, analysis.

　　

Using merge to perform both insert and update operations

We often have the need to look up one or more rows of data in a table based on a field or multiple fields, and if the lookup succeeds in getting a match, update one or more of the other fields, or, if the lookup fails, insert "one field or multiple fields" into the table as data from the new row. The first method is to update and then determine if there is a match based on the @ @rowcount, and if not, insert. Create a stored procedure with the following code first.

1 Use AdventureWorks
2 Go
3 createprocedure dbo. Insertunitmeasure @UnitMeasureCodenchar (3), @Namenvarchar (25)
4 AS
5 begin
6 SET NOCOUNT on;
7 update production.unitmeasure Set [email protected] [email protected]
8 if (@ @ROWCOUNT =0)
9 begin
Ten Insertinto production.unitmeasure (Name,unitmeasurecode) VALUES (@Name, @UnitMeasureCode)
One end
End
Go

Remember to see such a written question, the requirement is to insert the non-existent line, as long as the above statement in the update to select can be, then did not write out, now suddenly dawned, perhaps in the study @ @ROWCOUNT usage bar. This statement can also be implemented using the merge statement. Below we use the Merge keyword to modify this stored procedure.

1 alterprocedure dbo. Insertunitmeasure @UnitMeasureCodenchar (3), @Namenvarchar (25)
2 AS
3 begin
4 SET NOCOUNT ON
5 Merge Production.UnitMeasure as Target
6 using ([email protected], @Name) as source (Unitmeasurecode,name)
7 on (target. Unitmeasurecode=source. Unitmeasurecode)
8 when matched Thenupdateset Name=source. Name
9 Whennot matched Theninsert (unitmeasurecode,name) values (source. Unitmeasurecode,name)
deleted.* output, $action, Inserted.*into mytemptable;
One end
Go

This statement uses merge to modify the stored procedure, which in turn appears a keyword using and $action that I don't quite understand. The using is used to specify the data source that matches the table insertunitmeasure, where the data source is from the external input and is obtained by two input parameters. $action may be a placeholder that represents the action of the When clause above. As for inserted.* and deleted.* are the rows of data inserted and deleted, which I also mentioned in one of the articles, they are somewhat similar to the This keyword in the class, and can look at:SQL drip 14-edit data . Note In order to record the modification process we need to create a temporary table #mytemptable to track the modification process, so we need to make a new table before calling this stored procedure, as follows:

1 CreateTable mytemptable (
2 Existingcode nchar (3),
3 existingname nvarchar (50),
4 existingdate datetime,
5 Actiontaken nvarchar (50),
6 Newcode nchar (3),
7 [Newname]nvarchar (50),
8 Newdate datetime
9)
Ten Go

Now let's execute the following statement to see what the results are:

1 exec insertunitmeasure @UnitMeasureCode = ' ABC ', @Name = ' New Test Value1 '
2 EXEC insertunitmeasure @UnitMeasureCode = ' XYZ ', @Name = ' Test Value ';
3 EXEC insertunitmeasure @UnitMeasureCode = ' ABC ', @Name = ' another Test Valuea ';
4 Go

First use the statement: SELECT * from Production.UnitMeasure ORDER BY ModifiedDate desc To view data changes in the target table 1:

Figure 1

Although the stored procedure was executed three times, the first and third @unitmeasurecode values are the same ' ABC ' so the second time is definitely the update operation. So there are two new records in the final table. Then use the following statement to view trace information in the record table Mytemptable 2

Figure 2

We can see that the preceding two statements perform an insert operation, so the original values are empty because they do not exist until they are inserted. The third new is the update operation, update Unitmeasurecode for the ' ABC ' record.

　　

Use merge to perform insert and update operations in a single statement

In the AdventureWorks database, there are productinventory tables, which store inventory information, and the SalesOrderDetail table stores order information. Now if you subtract the number of orders that are placed on each product in the SalesOrderDetail table every day, update the quantity column in the ProductInventory table. If the number of orders over time causes the product inventory to drop to 0 or less, the row for that product is removed from the ProductInventory table. The following statement creates a stored procedure that implements the above logic.

1 Createprocedure production.usp_updateinventory
2 @OrderDatedatetime
3 AS
4 MERGE production.productinventory as Target
5 USING (SELECT ProductID, SUM (OrderQty) from Sales.SalesOrderDetail as Sod
6 JOIN Sales.SalesOrderHeader as Soh
7 on Sod. SalesOrderID = Soh. SalesOrderID
8 and Soh. OrderDate [email protected]
9 GROUPBY ProductID) as source (ProductID, OrderQty)
On (target. ProductID = source. ProductID)
One when matched and target. Quantity-source. OrderQty <=0
Thendelete
When matched
Thenupdateset Target. Quantity = target. Quantity-source. OrderQty,
Target. ModifiedDate =getdate ()
OUTPUT $action, Inserted.productid, inserted.quantity, Inserted.modifieddate, Deleted.productid,
Deleted.quantity, Deleted.modifieddate;
GO

This statement is more complicated than the first one, note that when the match is successful and the total is less than 0, it is possible to delete the record directly using a delete, and the output statement outputs the operation result directly, which is quite magical. Finally run the following statement to get 3 results. Note that this statement is equivalent to subtracting the order amount of May 1, 2003. If you run it more than once, the number of data bars in the entire table is reduced.

EXECUTE production.usp_updateinventory ' 20030501 '

Figure 3

　　

Use the merge table to perform update and insert operations on the target table

This time we have some table data that we want to compare to the data in this table, and if the Reasontype column in the table is updated with the Name field in the SalesReason table, the new row of data is inserted if there is no match sales.salesreason. Here you use table-valued constructors to specify multiple rows of the source table, use table variables to store update records, and note the scope of the table variables used. The code is as follows:

1 [email protected] (change varchar (20))
2 Merge into Sales.salesreason as Target
3 using (VALUES (' recommendation ', ' other '), (' Review ', ' Marketing '), (' Internet ', ' Promotion ')) as source ([NewName], Newreasontype)
4 on target. Name=source. [NewName]
5 when matched Thenupdateset Reasontype=source. Newreasontype
6 Whennot matched by Target Theninsert (Name,reasontype) VALUES ([Newname],newreasontype]
7 output $action [email protected];
8 Select Change,count (*) as countperchange [email protected] Change

After executing the above statement we get the following result description performed 2 insertions, 1 updates, 4. So is this the case, we look at Sales.salesreason This table found that there is already a ' Review ' this data, to it has been updated, the rest of the ' recommendation ', the ' Internet ' is to perform the insert operation. If you execute the above statement again, you will get the result of update 3, because these three data already exist, so we do update.

Figure 4

　　

Insert the results of the merge execution into another table

We can also write the results of the merge operation to another table, as the following statement writes each updated data message to a newly created table production.updatedinventory, with the following code:

1 Insertinto production.updatedinventory
2 SELECT ProductID, LocationID, Newqty, Previousqty
3 from
4 (MERGE production.productinventory as Target
5 USING (SELECT ProductID, SUM (OrderQty)
6 from Sales.SalesOrderDetail as Sod
7 JOIN Sales.SalesOrderHeader as Soh
8 on Sod. SalesOrderID = Soh. SalesOrderID
9 and Soh. OrderDate between ' 20030701 ' and ' 20030731 '
GROUPBY ProductID) as source (ProductID, OrderQty)
One on target. ProductID = source. ProductID
When matched and target. Quantity-source. OrderQty >=0
Thenupdateset Target. Quantity = target. Quantity-source. OrderQty
When matched and target. Quantity-source. OrderQty <=0
Thendelete
OUTPUT $action, Inserted.productid, Inserted.locationid, inserted.quantity as Newqty, deleted.quantity as PreviousQty )
+ as changes (Action, ProductID, LocationID, Newqty, previousqty) WHERE Action = ' UPDATE ';
GO

Executing this statement and querying the table to get the results like 5, we can see that the new sales are always less than the previous sales, because the execution of the order will be subtracted from the volume.

Figure 5

Here we only record the changes of the update, if you want to record all the operations can remove the last restriction of where action = ' UPDATE ', it is necessary to modify the structure of the record table, this and the second example is somewhat similar, but will be recorded in the actual table, The second example simply outputs these operational records.

Merge operation in SQL Server (reprint)