insert into C select * from A where not exists(select 1 from B where A.主键=B.主键)
insert c(...) select ... from a where not exists ( select 1 from b where 关键字段=a.关键字段 )
insert into c select a.* from a left join b on a.id=b.id where b.id is null --假设a,b两表之间通过id关联
declare @ta table(id int,name varchar(10)) declare @tb table(id int,name varchar(10)) declare @tc table(id int,name varchar(10)) insert @ta select 1,'abc' union all select 2,'efg' union all select 3,'hij' insert @tb select 1,'x' union all select 4,'y' union all select 5,'z'----查看b表中不存在的记录 SELECT a.id,a.name FROM @ta as a LEFT JOIN @tb as b on a.id = b.id WHERE b.id is null----将b表中不存在的记录插入到c表中 INSERT @tc(id,name) SELECT a.id,a.name FROM @ta as a LEFT JOIN @tb as b on a.id = b.id WHERE b.id is null----查看c表内容 select * from @tc/*结果 id name ----------- ---------- 2 efg 3 hij */
insert into c select * from a where not exists(select 1 from b where a.id=b.id)
select * from A
where not exists(select 1 from B where A.主键=B.主键)
select ... from a
where not exists (
select 1 from b
where 关键字段=a.关键字段
)
select a.* from a left join b on a.id=b.id where b.id is null
--假设a,b两表之间通过id关联
declare @tb table(id int,name varchar(10))
declare @tc table(id int,name varchar(10))
insert @ta
select 1,'abc' union all
select 2,'efg' union all
select 3,'hij'
insert @tb
select 1,'x' union all
select 4,'y' union all
select 5,'z'----查看b表中不存在的记录
SELECT a.id,a.name FROM @ta as a
LEFT JOIN @tb as b on a.id = b.id
WHERE b.id is null----将b表中不存在的记录插入到c表中
INSERT @tc(id,name)
SELECT a.id,a.name FROM @ta as a
LEFT JOIN @tb as b on a.id = b.id
WHERE b.id is null----查看c表内容
select * from @tc/*结果
id name
----------- ----------
2 efg
3 hij
*/
select * from a
where not exists(select 1 from b where a.id=b.id)
再问一下,管理-》作业 中可以执行存储过程吗?
但具体执行时需要在job启动的时候执行