SQL Server 2008 provides an enhanced SQL command merge for use in the msdn:http://msdn.microsoft.com/zh-cn/library/bb510625.aspx
Function: Inserts, updates, or deletes on the target table based on the results of joining 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 overall product list, a list of store products, you need to add products from the store to update the Total product list.
Total Product table, branch product table structure exactly the same:
Copy Code code as follows:
If object_id (' demo_allproducts ') is not null
drop table Demo_allproducts
Go
Create Table Demo_allproducts
(pkid int NOT null identity (1,1) primary key
, dname Nvarchar () null
, Dcode NVarchar () null
, ddate datetime NULL
)
Go
--this SQL is only for 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 (1,1) primary key
, dname Nvarchar () null
, Dcode NVarchar () null
, ddate datetime NULL
)
Go
--this SQL is only for 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 completely merge the store data into the Total Product table, based on the coded field, and replace the total product name with the store's product name if the product name is not.
If the Total Product table does not exist, add it.
Optional: If the list 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 store table are fully synchronized. The rows of the destination table may not need to be deleted in the actual operation.
Statement as follows:
Copy Code code as follows:
--Determine the target table
Merge into Demo_allproducts p
--Find the same 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
--Inserts the target table from the data source if it does not exist in the destination table
When isn't matched by Target Then Insert (dname,dcode,ddate) VALUES (s.dname,s.dcode,s.ddate)
--Deletes the source table row if the data source row does not exist in the source table
When isn't matched by Source Then Delete;
At this point, after the execution completes, the rows of the two tables are as follows:
Copy Code code 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 Code code as follows:
--Determine the target table
Merge into Demo_allproducts p
--Find the same 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
--Inserts the target table from the data source if it does not exist in the destination table
When isn't matched by Target Then Insert (dname,dcode,ddate) values (s.dname,s.dcode,s.ddate);
Results after execution:
Copy Code code 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 that affect, you can do so by using the @ @ROWCOUNT or Rowcount_big (), and the modified example is as follows:
Copy Code code as follows:
--Define table variables to store output
Declare @tableVarRecord Table
(mpkid int NOT null identity (1,1) 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 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
--Inserts the target table from the data source if it does not exist in the destination table
When isn't matched by Target Then
Insert (Dname,dcode,ddate) VALUES (s.dname,s.dcode,s.ddate)
--Deletes the source table row if the data source row does not exist in the source table
When isn't matched by Source Then
Delete OUTPUT deleted.* into @tableVarRecord;
----Delete OUTPUT inserted.* into @tableVarRecord;
--Returns the number of rows affected by the previous merge statement
SELECT @ @ROWCOUNT as Count1,rowcount_big () as Count2
SELECT * from @tableVarRecord;
Results:
Copy Code code as follows:
--Number of rows affected
--count1 Count2
--5 5
--deleted the 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 on @ @ROWCOUNT and rowcount_big () , please consult MSDN:
Http://technet.microsoft.com/zh-tw/library/ms187316.aspx
Http://msdn.microsoft.com/en-us/library/ms181406.aspx
If the effect is more than 2 billion, that is, the maximum range of the integral type, use the latter.
Invite the Month Note: This article copyright by invite month and the blog Garden Common All, reprint please indicate the source.
