这个问题应该很简单

[jianshao810]

insert into PB_FileDetail (theBusinessKey,theFileID,theFileTypeID,theFilelNote)
	select '123',theFileID,theFileTypeID,theFilelNote from PB_FileDetail where 
	theFileID in('a,b,c,d') 
    and not exists(select * from PB_FileDetail where theBusinessKey = '123' and theFileID in('a,b,c,d') 

我觉得上面的sql语句肯定又得优化的。
目的是想插入不存在重复的数据。

[Andy__Huang]

insert into PB_FileDetail(theBusinessKey,theFileID,theFileTypeID,theFilelNote)
select '123',theFileID,theFileTypeID,theFilelNote 
from PB_FileDetail a
where theFileID in('a,b,c,d') 
	and not exists(select 1 from PB_FileDetail b where a.theFileID=b.theFileID)

[Andy__Huang]

--a,b,c,d是键值,不是字符串,应该这样
insert into PB_FileDetail(theBusinessKey,theFileID,theFileTypeID,theFilelNote)
select '123',theFileID,theFileTypeID,theFilelNote 
from PB_FileDetail a
where theFileID in('a','b','c','d') 
	and not exists(select 1 from PB_FileDetail b where a.theFileID=b.theFileID)
或者
insert into PB_FileDetail(theBusinessKey,theFileID,theFileTypeID,theFilelNote)
select '123',theFileID,theFileTypeID,theFilelNote 
from PB_FileDetail a
where charindex(','+theFileID+',',','+'a,b,c,d'+',')>0 
	and not exists(select 1 from PB_FileDetail b where a.theFileID=b.theFileID)

[jianshao810]

谢谢hdhai9451 不过好像这两种跟我那句差距应该不大。因为这个表的数据量比较大。再查这个not exists 比较慢，看来我应该弄个临时表存起来啦

[Leon_He2014]

select '123',a.theFileID,a.theFileTypeID,a.theFilelNote 
from PB_FileDetail a
left join (
select theBusinessKey = '123','a' theFileID
union all select theBusinessKey = '123','b'
union all select theBusinessKey = '123','c'
union all select theBusinessKey = '123','d'
)  b
on a.theBusinessKey=b.theBusinessKey
and a.theFileID=b.theFileID
where b.theFileID is null

试试用join的方式

[jianshao810]

噢，因为 'a','b','c','d' 是动态传进来的。 所以，我还是用临时表存起来比较好啦。谢谢大家

[Andy__Huang]

try:

;with cte as
(
select theBusinessKey='123',theFileID,theFileTypeID,theFilelNote 
from PB_FileDetail a
where charindex(','+theFileID+',',','+'a,b,c,d'+',')>0 
)
insert into PB_FileDetail(theBusinessKey,theFileID,theFileTypeID,theFilelNote)
select theBusinessKey,theFileID,theFileTypeID,theFilelNote 
from cte a
where not exists(select 1 from PB_FileDetail b 
		where a.theBusinessKey=b.theBusinessKey 
			and a.theFileID=b.theFileID
			and a.theFileTypeID=b.theFileTypeID)

[唐诗三百首]

try this,

insert into PB_FileDetail(theBusinessKey,theFileID,theFileTypeID,theFilelNote)
 select '123',a.theFileID,a.theFileTypeID,a.theFilelNote 
 from (select * from PB_FileDetail where theFileID in('a,b,c,d')) a
 left join PB_FileDetail b on b.theBusinessKey='123' and a.theFileID=b.theFileID
 where b.theBusinessKey is null

[發糞塗牆]

用临时表存放动态传入的值，可以参考这个函数：

/*
功能说明：传入字符串跟分割符('''SGHE00000003'',''SGHE00000004'',''SGHE00000005''')，返回一个Table
*/
ALTER function [dbo].[fnSys_SplitString]
(
	---字符串分割
	@Str varchar(max), --传入的字符串
	@SeprateStr varchar(10)--分隔符
)
Returns @temp table(Code varchar(100)) --返回一个Table
As 
Begin
	Declare @i int	
	Set @Str=REPLACE(@str,'''','')
	Set @Str =rtrim(ltrim(@Str ))
	Set @i=charindex(@SeprateStr,@Str )
	While @i>=1
	Begin
		Insert @temp values(left(@Str ,@i-1))
		Set @Str =substring(@Str ,@i+1,len(@Str )-@i)
		Set @i=charindex(@SeprateStr,@Str )
	End
	If @Str <>'' 
		Insert @temp values(@Str )
	Return 
End

然后使用left join来实现not exists的逻辑，速度应该有所提升。

[阳泉酒家小当家]

可以先用上面的方法，分拆字符串，然后把结果插入到一个临时表。 然后再关联，这样应该是最有效的

[jianshao810]

真的大开眼界，这个小小的问题竟然有那么多解决方案，学习啦。。思路都很好。谢谢大家