INSERT INTO 表B SELECT * FROM 表A a WHERE NOT EXISTS (SELECT * FROM 表B WHERE phone=a.phone)
--建表 create table A(phone char(11),content varchar(20),[id] char(4))create table B(phone char(11),[name] varchar(20),[id] char(4))create table C(phone char(11),[name] varchar(20),[id] char(4))--建立触发器 create trigger inserttrig_A on [dbo].[A] for insert as begin declare @Counter int declare @CounterRow int set @Counter=0 set @CounterRow=(select count(*) from inserted) while @Counter<@CounterRow begin set @Counter=@Counter+1 if not exists (select * from inserted a inner join B on a.phone=B.phone) insert into C select * from inserted end end--插入初始数据 insert into B values('13300123111','私德','4') insert into C values('13300123111','私德','4') --插入测试数据 insert into A values('13300123111','内容1','1') insert into A values('13300123123','内容2私德','2') insert into A values('13300123124','内容3','3') --结果 select * from C /* phone name id ---------------------------------- 13300123111 私德 4 13300123123 内容2私德 2 13300123124 内容3 3 */
SELECT * FROM 表A a WHERE NOT EXISTS
(SELECT * FROM 表B WHERE phone=a.phone)
create table A(phone char(11),content varchar(20),[id] char(4))create table B(phone char(11),[name] varchar(20),[id] char(4))create table C(phone char(11),[name] varchar(20),[id] char(4))--建立触发器
create trigger inserttrig_A
on [dbo].[A]
for insert
as
begin
declare @Counter int
declare @CounterRow int
set @Counter=0
set @CounterRow=(select count(*) from inserted)
while @Counter<@CounterRow
begin
set @Counter=@Counter+1
if not exists (select * from inserted a inner join B on a.phone=B.phone)
insert into C select * from inserted
end
end--插入初始数据
insert into B values('13300123111','私德','4')
insert into C values('13300123111','私德','4')
--插入测试数据
insert into A values('13300123111','内容1','1')
insert into A values('13300123123','内容2私德','2')
insert into A values('13300123124','内容3','3')
--结果
select * from C
/*
phone name id
----------------------------------
13300123111 私德 4
13300123123 内容2私德 2
13300123124 内容3 3
*/