insert tab (a,b) select a=1,b=1001 where not exists (select 1 from tab where a=1)
上面语句因为有重复而不插入下面语句可以插入insert tab (a,b) select a=4,b=1001 where not exists (select 1 from tab where a=4)
或者这么写if not exists (select 1 from tab where a=1) insert tab (a,b) values( 1,1001)
if not exists(select * from table where a=2 and b=1001) insert into table values (1,1001)
if not exists(select * from table where a=2 and b=1001) insert into table values (2,1001)
if object_id('tb') is not null drop table tb go create table tb(a int,b int) go insert into tb select 1,1001 union all select 2,1002 union all select 3,1002 union all select 4,1003 go insert into tb select 1,1001 where not exists(select 1 from tb where a=1 and b=1001) insert into tb select 1,1001 where not exists(select 1 from tb where a=4 and b=1001)select * from tb
if not exists(select * from table where a=2 and b=1001) insert into table values (2,1001)
另外建立触发器: create trigger t1 on tb for insert as if exists(select 1 from tb,inserted where tb.a=inserted.a and tb.b=inserted.b) rollback
select a=1,b=1001 where not exists (select 1 from tab where a=1)
select a=4,b=1001 where not exists (select 1 from tab where a=4)
insert tab (a,b)
values( 1,1001)
insert into table values (1,1001)
insert into table values (2,1001)
if object_id('tb') is not null
drop table tb
go
create table tb(a int,b int)
go
insert into tb
select 1,1001 union all
select 2,1002 union all
select 3,1002 union all
select 4,1003
go
insert into tb select 1,1001 where not exists(select 1 from tb where a=1 and b=1001)
insert into tb select 1,1001 where not exists(select 1 from tb where a=4 and b=1001)select * from tb
insert into table values (2,1001)
create trigger t1
on tb
for insert
as
if exists(select 1 from tb,inserted where tb.a=inserted.a and tb.b=inserted.b)
rollback