SQLServer 2008 Merge 的机制探讨
SQL Server 2008 引入了一个Merge关键字,MSDN有对这个关键字的解释和范例。传送门:http://msdn.microsoft.com/zh-cn/library/bb510625.aspx 微软官方解释是: 根据与源表联接的结果,对目标表执行插入、更新或删除操作。 例如,根据在另一个表中找到的差异
SQL Server 2008 引入了一个Merge关键字,MSDN有对这个关键字的解释和范例。传送门:http://msdn.microsoft.com/zh-cn/library/bb510625.aspx 微软官方解释是:根据与源表联接的结果,对目标表执行插入、更新或删除操作。例如,根据在另一个表中找到的差异在一个表中插入、更新或删除行,可以对两个表进行同步。如果这个官方解释用白话文说就是:插入,更新和删除一步到位!
场景1:数据监测
这里有一个场景是,有一个旧总的商品表(AllProduct),还有一个新采集的商品表(Product)。总的商品表里面的数据比较陈旧,一些新的数据还没有加到里面去,而且有些旧的数据不正确。我们现在要根据商品Code来比对,如果旧表与新表的Code相同,但是名字不同,以新表的名字为准,更新旧表里面的商品名称。如果新的商品的记录在旧表里面不存在,那么把新表里面的数据插入到旧表里面去。新表和旧表的表结构完全相同。说了这么多,可能有点绕,那么总结下就是:
1)两张表(新表和旧表)的结构相同数据不同;
2)新表里面的数据如果不存在(根据主键进行匹配)于旧表,则把新表的数据插入旧表
3)旧表中的记录的ID如果和新表的记录的ID相同,但是Name不同,则将更新旧表中的Name为新表中的Name
初始化环境的Sql代码如下:
if OBJECT_ID('AllProducts') is not null drop table AllProducts go Create table AllProducts ( P_ID int not null identity(1,1) primary key, P_Name Nvarchar(20) null, P_Code NVarchar(30) null, P_Date datetime null ) go Insert into AllProducts (P_Name,P_Code,P_Date) values ('ProductA','Pro_A',GETDATE()), ('ProductB','Pro_B',GETDATE()), ('ProductB','Pro_C',GETDATE()), ('ProductD','Pro_D',GETDATE()), ('ProductE','Pro_E',GETDATE()) if OBJECT_ID('Product') is not null drop table Product go Create table Product ( P_ID int not null identity(1,1) primary key, P_Name Nvarchar(20) null, P_Code NVarchar(30) null, P_Date datetime null ) go Insert into Product (P_Name,P_Code,P_Date) values ('ProductA','Pro_A',GETDATE()), ('ProductC','Pro_C',GETDATE()), ('ProductF','Pro_F',GETDATE()) select * from AllProducts; select * from Product;
输出结果如下:
根据我们确定的规则,即将发生如下更新和插入:
1)更新:AllProduct表中的name为Pro_A和name为Pro_C的记录会被匹配到,然后由于Pro_C对应的Name不同,所以AllProecudt表中的Name会被更新。
2)插入:AllProduct表中没有Pro_F这条记录,所以会被插入到AllProduct表中
我们使用Merge的sql如下:
--确定目标表 Merge Into AllProducts p --从数据源查找编码相同的产品 using Product s on p.P_Code=s.P_Code --如果编码相同,则更新目标表的名称 When Matched and P.P_Names.P_Name Then Update set P.P_Name=s.P_Name --如果目标表中不存在,则从数据源插入目标表 --When Not Matched By Target Then Insert (DName,DCode,DDate) values (s.DName,s.DCode,s.DDate); When Not Matched By Target Then Insert values (s.P_Name,s.P_Code,s.P_Date);
上面注释的那条语句和下面的那个意义相同,只是没有那么简洁。执行结果如下:
第一条记录已经被更新,第二条记录是从Product表中插入的,是不是真的很简洁。
场景2:数据表同步
如果希望AllProdct和Product表一模一样,即数据同步,那么我们需要删掉Product表中不存在于AllProduct表中的记录。整个sql如下:
--确定目标表 Merge Into AllProducts p --从数据源查找编码相同的产品 using Product s on p.P_Code=s.P_Code --如果编码相同,则更新目标表的名称 When Matched and P.P_Names.P_Name Then Update set P.P_Name=s.P_Name --如果目标表中不存在,则从数据源插入目标表 --When Not Matched By Target Then Insert (DName,DCode,DDate) values (s.DName,s.DCode,s.DDate); When Not Matched By Target Then Insert values (s.P_Name,s.P_Code,s.P_Date) --如果数据源的行在目标表中不存在,则删除源表行 When Not Matched By Source Then Delete;
总结:Merge是同时做插入和更新,效率据说更高。

Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics

For objects with the same name that already exist in the SQL Server database, the following steps need to be taken: Confirm the object type (table, view, stored procedure). IF NOT EXISTS can be used to skip creation if the object is empty. If the object has data, use a different name or modify the structure. Use DROP to delete existing objects (use caution, backup recommended). Check for schema changes to make sure there are no references to deleted or renamed objects.

The import steps are as follows: Copy the MDF file to SQL Server's data directory (usually C:\Program Files\Microsoft SQL Server\MSSQL\DATA). In SQL Server Management Studio (SSMS), open the database and select Attach. Click the Add button and select the MDF file. Confirm the database name and click the OK button.

HQL and SQL are compared in the Hibernate framework: HQL (1. Object-oriented syntax, 2. Database-independent queries, 3. Type safety), while SQL directly operates the database (1. Database-independent standards, 2. Complex executable queries and data manipulation).

When the SQL Server service fails to start, here are some steps to resolve: Check the error log to determine the root cause. Make sure the service account has permission to start the service. Check whether dependency services are running. Disable antivirus software. Repair SQL Server installation. If the repair does not work, reinstall SQL Server.

To view the SQL Server port number: Open SSMS and connect to the server. Find the server name in Object Explorer, right-click it and select Properties. In the Connection tab, view the TCP Port field.

SQL Server database files are usually stored in the following default location: Windows: C:\Program Files\Microsoft SQL Server\MSSQL\DATALinux: /var/opt/mssql/data The database file location can be customized by modifying the database file path setting.

If you accidentally delete a SQL Server database, you can take the following steps to recover: stop database activity; back up log files; check database logs; recovery options: restore from backup; restore from transaction log; use DBCC CHECKDB; use third-party tools. Please back up your database regularly and enable transaction logging to prevent data loss.

If the SQL Server installation fails, you can clean it up by following these steps: Uninstall SQL Server Delete registry keys Delete files and folders Restart the computer
