添加不存在记录的3种方法

当我们A表、B表都包含相同信息（如用户信息），现在需要向A表插入在B表中不存在的纪录。我们有3种常见的方法来完成

1 insert A
select * from B
where id not in (select id from A)

2 insert A
select * from B
where not EXISTS(SELECT 1 FROM a WHERE a.ID=b.ID)

3 select b.* from B
left join A on a.id=b.id
where a.id is null

让我们来看看测试

-- 原始数据表
create table #source_user (userid int, username varchar(20))
-- 目标表
create table  #mid_user (userid int, username varchar(20))

-- 为两表建立索引
CREATE UNIQUE CLUSTERED INDEX Ix_Temp_source_user_Userid ON #source_user(UserID);
CREATE UNIQUE CLUSTERED INDEX Ix_Temp_mid_user_Userid ON #mid_user(UserID);

-- 建立5万测试数据，其中为目标表建立id小于2万的用户
declare @i int
set @i = 1
while @i < 50000
begin
    insert #source_user
    select @i, 'user '+convert(varchar,@i)

    if @i < 20000
    insert #mid_user
    select @i, 'user '+convert(varchar,@i)

    set @i = @i + 1
end

declare @t1 datetime

-- 1 使用 not in 模式插入
-- 实际执行计划：估计子树大小6.637（无索引）, 6.19（有索引）
-- 有索引模式下执行时间（秒） 02.750

begin tran
set @t1 = Getdate()

insert #mid_user
select userid, username
from #source_user s
where userid not in (select userid from #mid_user)

print convert(varchar(32), getdate()-@t1, 121)
rollback

-- 2 使用 not EXISTS 模式插入
-- 实际执行计划：估计子树大小1.43627（无索引）, 0.688897（有索引）
-- 有索引模式下执行时间（秒） 0.432

begin tran
set @t1 = Getdate()

insert #mid_user
select userid, username
from #source_user s
where not EXISTS(SELECT 1 FROM #mid_user r WHERE r.[UserID]=s.[UserID])

print convert(varchar(32), getdate()-@t1, 121)
rollback

-- 3 使用 left join  where  m.userid is null 模式插入
-- 实际执行计划：估计子树大小1.48（无索引）, 0.788897（有索引）
-- 有索引模式下执行时间（秒） 0.432

begin tran
set @t1 = Getdate()

insert #mid_user
select s.userid, s.username
from #source_user s
left join #mid_user m on s.userid=m.userid
where  m.userid is null

print convert(varchar(32), getdate()-@t1, 121)
rollback

-- 检查数据
select count(1) from #source_user
select count(1) from #mid_user
select * from #source_user
select * from #mid_user
-- 删除测试数据
-- drop index Ix_Temp_source_user_Userid ON #source_user
-- drop index Ix_Temp_mid_user_Userid ON #mid_user
drop table #source_user
drop table #mid_user

根据结果，我们可以看到，使用第2种方法最快：Not Exists
select userid, username
from #source_user s
where not EXISTS(SELECT 1 FROM #mid_user r WHERE r.[UserID]=s.[UserID])