典型的程序代码思维。。在DB中,直接做个Left or Right Join ... Where Is Null就OK
可以这么写:insert into b select * from a where not exists(select 1 from b where a.a = b.a and a.b = b.b)
或者:insert into b select * from a left join b on a.a = b.a and a.b = b.b where b.a is null
--这玩意不经常练习还真容易忘,哎create table A ( id int, Name nvarchar(20), Re nvarchar(50) ) create table B ( id int, Name nvarchar(20), Address nvarchar(50), Re nvarchar(50) )insert into A values(1,'张三','测试张三'); insert into A values(2,'李四','测试李四'); insert into A values(3,'王五','测试王五');insert into B values(1,'张三','测试张三',''); insert into B values(2,'赵六','测试赵六',''); insert into B values(3,'孙七','测试孙七','');--无需两个表结果完全一致 insert into B (ID,Name) select id,Name from ( select id,Name from A except select id,Name from B )tMERGE B --要处理的表 USING A --参照的表 ON A.ID=B.ID and A.Name=B.Name --关联条件 WHEN NOT MATCHED THEN INSERT(id,Name) VALUES(A.ID,A.Name); --如果要处理表没有参照表上的记录,则插入 --WHEN MATCHED THEN UPDATE SET A.ACOL=B.BCOL --如果记录匹配,就更新目标表的匹配行 --WHEN NOT MATCHED BY SOURCE THEN DELETE --如果要处理表的记录在参照表上不存在,则删除
insert into b(a,b)--指定插入列 select a,b --指定插入列 from A where not exists(select 1 from B where a=a.a and b=a.b)
采用下面的SQL进行数据批量插入到B表中,10W级别的数据量不算大,后续如果B表中的数据增加到百万级,可能会插入速度明显变慢,这个时候建议考虑对B表进行重建索引,现在可以在B表的a,b字段上建索引。insert into b select * from a left join b on a.[a] = b.[a] and a.[b] = b.[b] where a is null and b is null
就是用not exists写法,大神 你知道这种写法的数据库软件是怎么执行的吗 就是这句话是如何工作的 select * from A x where not exists(select 1 from B y where x.a=y.a or x.b=y.b )
not exists加上or,很难利用到高效索引,所以慢
SELECT a.* FROM a x LEFT JOIN b y ON x.a = y.a LEFT JOIN b z ON x.b = z.b WHERE x.a IS NOT NULL 试试
select *
from a
where not exists(select 1 from b where a.a = b.a and a.b = b.b)
select *
from a
left join b
on a.a = b.a and a.b = b.b
where b.a is null
--这玩意不经常练习还真容易忘,哎create table A
(
id int,
Name nvarchar(20),
Re nvarchar(50)
)
create table B
(
id int,
Name nvarchar(20),
Address nvarchar(50),
Re nvarchar(50)
)insert into A values(1,'张三','测试张三');
insert into A values(2,'李四','测试李四');
insert into A values(3,'王五','测试王五');insert into B values(1,'张三','测试张三','');
insert into B values(2,'赵六','测试赵六','');
insert into B values(3,'孙七','测试孙七','');--无需两个表结果完全一致
insert into B (ID,Name)
select id,Name from
(
select id,Name from A
except
select id,Name from B
)tMERGE B --要处理的表
USING A --参照的表
ON A.ID=B.ID and A.Name=B.Name --关联条件
WHEN NOT MATCHED THEN INSERT(id,Name) VALUES(A.ID,A.Name); --如果要处理表没有参照表上的记录,则插入
--WHEN MATCHED THEN UPDATE SET A.ACOL=B.BCOL --如果记录匹配,就更新目标表的匹配行
--WHEN NOT MATCHED BY SOURCE THEN DELETE --如果要处理表的记录在参照表上不存在,则删除
select a,b --指定插入列
from A where not exists(select 1 from B where a=a.a and b=a.b)
select *
from a
left join b
on a.[a] = b.[a] and a.[b] = b.[b]
where a is null and b is null
select * from A x where not exists(select 1 from B y where x.a=y.a or x.b=y.b )
FROM a x
LEFT JOIN b y ON x.a = y.a
LEFT JOIN b z ON x.b = z.b
WHERE x.a IS NOT NULL 试试