当我们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])