SQL Server 2008 provides an enhanced SQL command Merge. For usage, see MSDN:/zh-cn/library/
Function: Perform insert, update or delete operations on the target table based on the results of joining with the source table. For example, two tables can be synchronized based on differences found in another table.
Let's look at an example. If there is a total product list and a branch product list, the total product list needs to be updated when adding products from the branch.
The total product table and branch product table structure are completely consistent:
Copy the 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(1,1) primary key
,DName Nvarchar(20) null
,DCode NVarchar(30) 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(20) null
,DCode NVarchar(30) 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
Assume that the branch data needs to be fully merged into the total product table now, based on the encoded field, and if the product name does not match, replace the total product name with the branch's product name.
If it does not exist in the total product table, add it.
Optional: If the branch table does not exist, delete rows that are not in the branch from the total product table. If so, the total product table and branch table will be completely synchronized. In actual operation, it may not be necessary to delete rows of the target table.
The statements are as follows:
Copy the codeThe code is as follows:
--Determine the target table
Merge Into Demo_AllProducts p
--Finding products with the same encoding from the data source
using Demo_Shop1_Product s on =
--If the encoding is the same, update the name of the target table
When Matched and <> Then Update set =
--If the target table does not exist, insert the target table from the data source
When Not Matched By Target Then Insert (DName,DCode,DDate) values (,,)
--If the row of the data source does not exist in the source table, delete the source table rows
When Not Matched By Source Then Delete;
At this time, after the execution is completed, the rows of both tables are as follows:
Copy the 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 the codeThe code is as follows:
--Determine the target table
Merge Into Demo_AllProducts p
--Finding products with the same encoding from the data source
using Demo_Shop1_Product s on =
--If the encoding is the same, update the name of the target table
When Matched and <> Then Update set =
--If the target table does not exist, insert the target table from the data source
When Not Matched By Target Then Insert (DName,DCode,DDate) values (,,);
After execution results:
Copy the 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. If you only need to know the number of rows affected, you can use @@ROWCOUNT or ROWCOUNT_BIG(). The modified example is as follows:
Copy the codeThe code is 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(20) null
,DCode NVarchar(30) null
,DDate datetime null
)
--Determine the target table
Merge Into Demo_AllProducts p
--Finding products with the same encoding from the data source
using Demo_Shop1_Product s on =
--If the encoding is the same, update the name of the target table
When Matched and <> Then
Update set =
--If the target table does not exist, insert the target table from the data source
When Not Matched By Target Then
Insert (DName,DCode,DDate) values (,,)
--If the row of the data source does not exist in the source table, delete the source table rows
When Not Matched By Source Then
Delete OUTPUT deleted.* INTO @tableVarRecord;
----Delete OUTPUT Inserted.* INTO @tableVarRecord;
--Return the number of rows affected by the previous Merge statement
select @@ROWCOUNT as Count1,ROWCOUNT_BIG() as Count2
select * from @tableVarRecord;
result:
Copy the codeThe code is as follows:
--The number of rows affected
--Count1 Count2
--5 5
--Deleted table rows
--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
about@@ROWCOUNTandROWCOUNT_BIG()For more instructions, please refer to MSDN:
/zh-tw/library/
/en-us/library/
If the impact results exceed 2 billion, that is, the maximum range of integers, please use the latter.