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