现在有个需求，一次性insert 2000条以上的数据。我用了存储过程来处理。
代码如下
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[SendChitOfHead]
@CsendmemberId int,
@CfrommemberIds varchar(8000),---
@CpersonIds varchar(8000),---
@CpersonType varchar(500),
@Ctitle varchar(500),
@CContent text,
@Ctype varchar(500),
@CoperateIP varchar(500),
@Cstate varchar(500),
@CSid int,
@CCids varchar(8000) ,---
@CphoneNums varchar(8000)---
as
begin
declare @CfrommemberId varchar(8000)
declare @CpersonId varchar(8000)
declare @CCid varchar(8000)
declare @CphoneNum varchar(8000)
set @CfrommemberIds=@CfrommemberIds+','
set @CpersonIds=@CpersonIds+','
set @CCids=@CCids+','
set @CphoneNums=@CphoneNums+','
while(@CfrommemberIds<>'')
begin
set @CfrommemberId=convert(VARCHAR,left(@CfrommemberIds,Charindex(',',@CfrommemberIds,1)-1))
set @CpersonId=convert(VARCHAR,left(@CpersonIds,Charindex(',',@CpersonIds,1)-1))
set @CCid=convert(VARCHAR,left(@CCids,Charindex(',',@CCids,1)-1))
set @CphoneNum=convert(VARCHAR,left(@CphoneNums,Charindex(',',@CphoneNums,1)-1))
-----print @id
insert into Sys_chit(CsendmemberId,CfrommemberId,CpersonId,CpersonType,Ctitle,CContent,Ctype,CoperateIP,Csendtime,Cstate,CSid,CCid,CsendState,CphoneNum,COperator) values(@CsendmemberId,@CfrommemberId,@CpersonId,@CpersonType,@Ctitle,@CContent,@Ctype,@CoperateIP,getdate(),@Cstate,@CSid,@CCid,' ',@CphoneNum,' ')
set @CfrommemberIds=stuff(@CfrommemberIds,1,charindex(',',@CfrommemberIds,1),'')
set @CpersonIds=stuff(@CpersonIds,1,charindex(',',@CpersonIds,1),'')
set @CCids=stuff(@CCids,1,charindex(',',@CCids,1),'')
set @CphoneNums=stuff(@CphoneNums,1,charindex(',',@CphoneNums,1),'')
end
end
本地测试2300条数据，时间大概是1分钟左右。
代码中是将字符串中包含逗号的分割，然后循环插入。
本人初学者，哪位高手能再优化下代码，提高效率。
或者其他更优写法。
在线等。
------解决方案--------------------
DROP TABLE test
CREATE TABLE test (NAME VARCHAR(max))
INSERT INTO test VALUES('15874,154874,15474,15474,1547,154748,1584847')
go
select
SUBSTRING(a.NAME,number,CHARINDEX(',',a.NAME+',',number)-number) as spnum,'内容' ct
from
test a,master..spt_values
where
number >=1 and number<=len(a.NAME)
and type='p'
and substring(','+a.NAME,number,1)=','
/*
spnum ct
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----
15874 内容