虽然不是很懂你的意思,但是从例子中我觉得你是想两个列不能重复,如果是这样,对AB两列同时做一个唯一索引即可,下面给个例子你:CREATE TABLE #t(a INT,b int) INSERT INTO #t ( a, b ) VALUES ( 100,200 )--创建唯一索引: CREATE UNIQUE INDEX test ON #t(a,b)--测试: INSERT INTO #t VALUES(100,200) /* Msg 2601, Level 14, State 1, Line 1 Cannot insert duplicate key row in object 'dbo.#t' with unique index 'test'. The duplicate key value is (100, 200). The statement has been terminated.*/INSERT INTO #t SELECT 100,100 UNION ALL SELECT 200,200/* (2 row(s) affected) */
可以的,比较简单的方法就是,通过创建一个唯一索引,来限制不能插入重复数据:if object_id('tb') is not null drop table tb goCREATE TABLE tb(a INT,b int)--创建唯一索引 create unique index uni_idx_tb_ab ON tb(a,b) --插入第1条数据 INSERT INTO tb( a, b ) VALUES ( 100,200)--插入第2条数据,不会报错 INSERT INTO tb values(100,100)--插入第3条数据,不会报错 insert into tb SELECT 200,200 --插入第4条数据,报错,实现了你的要求 INSERT INTO tb VALUES(100,200) /* 消息 2601,级别 14,状态 1,第 4 行 不能在具有唯一索引 'uni_idx_tb_ab' 的对象 'dbo.tb' 中插入重复键的行。 语句已终止。 */
;with cte as ( select A from #tb union all select B from #tb ) INSERT INTO #tb select * from ( select a.A,b.A as B from cte a,cte b )t where not exists(select 1 from #tb a where a.A=t.A and a.B=t.B )select * from #tb drop table #tb/* A B 100 200 -->原本已经插入 100 100 200 100 200 200 */
用触发器实现.create table ss(A int,B int)insert into ss(A,B) select 100,200 -- 建触发器 create trigger tr_ss on ss instead of insert as begin insert into ss(A,B) select x.A,x.B from inserted x left join ss y on x.A=y.A and x.B=y.B where y.A is null and y.B is null end -- 测试 insert into ss(A,B) select 100,100 union all select 200,200 union all select 100,200-- 结果 select A,B from ss/* A B ----------- ----------- 100 200 --> 原有记录 100 100 --> 能够写入 200 200 --> 能够写入(3 row(s) affected) */
INSERT INTO #t
( a, b )
VALUES ( 100,200
)--创建唯一索引:
CREATE UNIQUE INDEX test ON #t(a,b)--测试:
INSERT INTO #t
VALUES(100,200)
/*
Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'dbo.#t' with unique index 'test'. The duplicate key value is (100, 200).
The statement has been terminated.*/INSERT INTO #t
SELECT 100,100
UNION ALL
SELECT 200,200/*
(2 row(s) affected)
*/
drop table tb
goCREATE TABLE tb(a INT,b int)--创建唯一索引
create unique index uni_idx_tb_ab ON tb(a,b)
--插入第1条数据
INSERT INTO tb( a, b )
VALUES ( 100,200)--插入第2条数据,不会报错
INSERT INTO tb
values(100,100)--插入第3条数据,不会报错
insert into tb
SELECT 200,200
--插入第4条数据,报错,实现了你的要求
INSERT INTO tb
VALUES(100,200)
/*
消息 2601,级别 14,状态 1,第 4 行
不能在具有唯一索引 'uni_idx_tb_ab' 的对象 'dbo.tb' 中插入重复键的行。
语句已终止。
*/
(
select A from #tb union all select B from #tb
)
INSERT INTO #tb
select *
from (
select a.A,b.A as B
from cte a,cte b
)t
where not exists(select 1 from #tb a where a.A=t.A and a.B=t.B )select * from #tb
drop table #tb/*
A B
100 200 -->原本已经插入
100 100
200 100
200 200
*/
select 100,200
-- 建触发器
create trigger tr_ss
on ss instead of insert
as
begin
insert into ss(A,B)
select x.A,x.B
from inserted x
left join ss y on x.A=y.A and x.B=y.B
where y.A is null and y.B is null
end
-- 测试
insert into ss(A,B)
select 100,100 union all
select 200,200 union all
select 100,200-- 结果
select A,B from ss/*
A B
----------- -----------
100 200 --> 原有记录
100 100 --> 能够写入
200 200 --> 能够写入(3 row(s) affected)
*/