Merge into 用法

Merge into ：源表和目标表进行对比，如果符合条件，则更新目标表中的数据，如果不符合条件，则将数据插入到目标表中；

注意：源表中不能存在重复数据

例子：

表结构如下：

表一：

表二：

源表：表一中的数据

CREATE TABLE [dbo].[Member](
	[MID] [int] IDENTITY(1,1) NOT NULL,
	[MName] [char](50) NULL,
 CONSTRAINT [PK_Member] PRIMARY KEY CLUSTERED 
(
	[MID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[Member] ON 

INSERT [dbo].[Member] ([MID], [MName]) VALUES (1, N'张萨                                              ')
INSERT [dbo].[Member] ([MID], [MName]) VALUES (2, N'王强                                              ')
INSERT [dbo].[Member] ([MID], [MName]) VALUES (3, N'李三                                              ')
INSERT [dbo].[Member] ([MID], [MName]) VALUES (4, N'李四                                              ')
INSERT [dbo].[Member] ([MID], [MName]) VALUES (5, N'阳阳                                              ')
INSERT [dbo].[Member] ([MID], [MName]) VALUES (6, N'虎子                                              ')
INSERT [dbo].[Member] ([MID], [MName]) VALUES (7, N'夏雪                                              ')
INSERT [dbo].[Member] ([MID], [MName]) VALUES (8, N'璐璐                                              ')
INSERT [dbo].[Member] ([MID], [MName]) VALUES (9, N'珊珊                                              ')
INSERT [dbo].[Member] ([MID], [MName]) VALUES (10, N'香奈儿                                            ')
SET IDENTITY_INSERT [dbo].[Member] OFF
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'学号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Member', @level2type=N'COLUMN',@level2name=N'MID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'姓名' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Member', @level2type=N'COLUMN',@level2name=N'MName'
GO

将表二和表一进行对比

Merge into Member_sync as S 
using Member as T
on(S.MID = T.MID)
when matched 
then update set S.MName = T.MName
when not matched 
then insert([MName]) values (T.MName);


select * from Member_sync

　　修改表一中的一条数据

执行上面的Merge Into 代码可以看到如下结果

 推荐阅读：https://blog.csdn.net/spw55381155/article/details/79891305