34,576
社区成员
发帖
与我相关
我的任务
分享
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')
/*
功能说明:传入字符串跟分割符('''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的逻辑,速度应该有所提升。
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
;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)
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的方式--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)
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)