求一个批量插入，二个字段不能重复的语句
有两个表table1，table2
我用insert into table1 from table2 的语句进行插入
table1 和table2 都有字段 a，b
要求插入时 table1里 a和b字段不能同时都重复
就是已经插入一次的行不能再插入了，防止用户连点两次按钮时插入重复的值
[解决办法]
if object_id('Tempdb..#table1') is not null drop table #table1
if object_id('Tempdb..#table2') is not null drop table #table2
create table #table1(
id int identity(1,1) not null,
a nvarchar(100) null,
b nvarchar(100) null
)
create table #table2(
id int identity(1,1) not null,
a nvarchar(100) null,
b nvarchar(100) null
)
Insert Into #table1
select 'a','b' union all
select 'c','d' union all
select 'e','f' union all
select 'g','h'
Insert Into #table2
select 'a','b' union all
select 'c','d' union all
select 'e','s' union all
select 'g','h'
--查询相同数据
;with cte_a as (
select t.id from #table2 t
join #table1 s on s.a=t.a and s.b=t.b
)
--查询table1不存在的数据
,cte_b as(
select * from #table2 where id not in(select id from cte_a)
)
--插入
Insert into #table1
select a,b from cte_b
--查询Insert 结果
select * from #table1
---------------------------------------
--------结果
id a b
----------- ---------------------------------------------------------------- ----------------------------------------------------------------
1 a b
2 c d
3 e f
4 g h
5 e s
(5 行受影响)
--或者效率高一些，就用这个
Insert Into #table1
select a,b from
(select p.a,p.b,s.id as col from #table2 p left join #table1 s on s.a=p.a and s.b=p.b) t
where col is null
