SQL Enhanced three Merge (using Insert,update,delete in a single statement) in Server 2008
SQL Server 2008 provides an enhanced SQL command merge, usage see msdn:http://msdn.microsoft.com/zh-cn/library/bb510625.aspx
Function: Performs an INSERT, update, or delete operation on the target table based on the results of the join with the source table. For example, you can synchronize two tables by inserting, updating, or deleting rows in a table based on differences found in another table.
Let's take a look at an example, if there is a list of overall products, a list of branch products that need to update the Total product list when adding a product from a branch.
Total Product table, branch product table structure exactly the same:
Copy CodeThe code is as follows:
If object_id (' demo_allproducts ') is not null
drop table Demo_allproducts
Go
Create Table Demo_allproducts
(PKID int NOT null identity (primary) key
, Dname Nvarchar (+) NULL
, DCode NVarchar (+) NULL
, ddate datetime NULL
)
Go
--this SQL Server 2008
Insert into Demo_allproducts
(dname,dcode,ddate)
Values
(' Demoa ', ' AAA ', GETDATE ()),
(' demob ', ' BBB ', GETDATE ()),
(' Democ ', ' CCC ', GETDATE ()),
(' Demod ', ' DDD ', GETDATE ()),
(' DeMoe ', ' EEE ', GETDATE ())
SELECT * FROM Demo_allproducts
--pkid dname DCode ddate
--1 Demoa AAA 2010-10-12 20:33:54.417
--2 demob BBB 2010-10-12 20:33:54.417
--3 Democ CCC 2010-10-12 20:33:54.417
--4 demod DDD 2010-10-12 20:33:54.417
--5 DeMoe EEE 2010-10-12 20:33:54.417
If object_id (' demo_shop1_product ') is not null
drop table Demo_shop1_product
Go
Create Table Demo_shop1_product
(PKID int NOT null identity (primary) key
, Dname Nvarchar (+) NULL
, DCode NVarchar (+) NULL
, ddate datetime NULL
)
Go
--this SQL Server 2008
Insert into Demo_shop1_product
(dname,dcode,ddate)
Values
(' Demoa ', ' AAA ', GETDATE ()),
(' demob ', ' CCC ', GETDATE ()),
(' Demof ', ' FFF ', GETDATE ())
SELECT * FROM Demo_shop1_product
--pkid dname DCode ddate
--1 Demoa AAA 2010-10-17 20:19:32.767
--2 demob CCC 2010-10-17 20:19:32.767
--3 demof FFF 2010-10-17 20:19:32.767
Suppose you now need to fully merge the store data into the Total Product table, based on the coded field, and if the product name does not, replace the total product name with the product name of the branch.
If it does not exist in the Total product table, it is added.
Optional: If the branch table does not exist, delete the rows that are not in the store from the Total Product table. If so, the total Product table and the branch table are fully synchronized. The row for the target table may not need to be deleted in the actual operation.
The statements are as follows:
Copy CodeThe code is as follows:
--Determine the target table
Merge into Demo_allproducts p
--Find the same encoded product from the data source
Using Demo_shop1_product s on P.dcode=s.dcode
--Update the name of the target table if the encoding is the same
When matched and P.dname<>s.dname then Update set P.dname=s.dname
--Insert the target table from the data source if it does not exist in the target table
When not matched by Target then Insert (dname,dcode,ddate) VALUES (s.dname,s.dcode,s.ddate)
--Delete the source table row if the row of the data source does not exist in the source table
When not matched by Source then Delete;
At this point, when execution is complete, the rows for the two tables are as follows:
Copy CodeThe code is as follows:
--pkid dname DCode ddate
--1 Demoa AAA 2010-10-17 20:31:00.827
--2 demob CCC 2010-10-17 20:31:00.827
--3 demof FFF 2010-10-17 20:31:00.827
If not deleted, the statement is as follows:
Copy CodeThe code is as follows:
--Determine the target table
Merge into Demo_allproducts p
--Find the same encoded product from the data source
Using Demo_shop1_product s on P.dcode=s.dcode
--Update the name of the target table if the encoding is the same
When matched and P.dname<>s.dname then Update set P.dname=s.dname
--Insert the target table from the data source if it does not exist in the target table
When not matched by Target then Insert (dname,dcode,ddate) values (s.dname,s.dcode,s.ddate);
Results after execution:
Copy CodeThe code is as follows:
--pkid dname DCode ddate
--1 Demoa AAA 2010-10-17 20:30:28.350
--2 demob BBB 2010-10-17 20:30:28.350
--3 demob CCC 2010-10-17 20:30:28.350
--4 demod DDD 2010-10-17 20:30:28.350
--5 DeMoe EEE 2010-10-17 20:30:28.350
--6 demof FFF 2010-10-17 20:31:00.827
--pkid dname DCode ddate
--1 Demoa AAA 2010-10-17 20:31:00.827
--2 demob CCC 2010-10-17 20:31:00.827
--3 demof FFF 2010-10-17 20:31:00.827
If you need to record the rows affected by the merge statement, you can use the OUTPUT clause, and if you only need to know the number of rows affected, you can have the @ @ROWCOUNT or Rowcount_big (), and the modified example is as follows:
Copy CodeThe code is as follows:
--Define table variables to store output
Declare @tableVarRecord Table
(mpkid int NOT null identity (primary) key
, PKID int NULL
, Dname Nvarchar (+) NULL
, DCode NVarchar (+) NULL
, ddate datetime NULL
)
--Determine the target table
Merge into Demo_allproducts p
--Find the same encoded product from the data source
Using Demo_shop1_product s on P.dcode=s.dcode
--Update the name of the target table if the encoding is the same
When matched and P.dname<>s.dname then
Update Set P.dname=s.dname
--Insert the target table from the data source if it does not exist in the target table
When not matched by Target then
Insert (Dname,dcode,ddate) VALUES (s.dname,s.dcode,s.ddate)
--Delete the source table row if the row of the data source does not exist in the source table
When not matched by Source then
Delete OUTPUT deleted.* into @tableVarRecord;
----Delete OUTPUT inserted.* into @tableVarRecord;
--Returns the number of rows affected by the last merge statement
SELECT @ @ROWCOUNT as Count1,rowcount_big () as Count2
SELECT * from @tableVarRecord;
Results:
Copy CodeThe code is as follows:
--Number of rows affected
--count1 Count2
--5 5
--deleted rows of a table
--mpkid PKID dname DCode ddate
--1 null NULL NULL NULL
--2 2 demob BBB 2010-10-17 21:42:30.700
--3 3 Democ CCC 2010-10-17 21:42:30.700
--4 4 demod DDD 2010-10-17 21:42:30.700
--5 5 DeMoe EEE 2010-10-17 21:42:30.700
For more information about @ @ROWCOUNT and rowcount_big () , check out MSDN:
Http://technet.microsoft.com/zh-tw/library/ms187316.aspx
Http://msdn.microsoft.com/en-us/library/ms181406.aspx
Use the latter if the effect results in more than 2 billion, which is the maximum range for the integer type.
Http://www.jb51.net/article/27091.htm
SQL Enhanced three Merge (used in a single statement) in Server 2008
