sqlserver存储过程批量插入数据

在系统中经常会遇到向数据库中批量插入数据情况，存储过程中没有数组，只有通过字符串分割循环插入，下面是一个本人研究的一个例子：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

create  proc [dbo].[Proc_TestBatchMainDetailIns] @mainName nvarchar(50),@detailNameStr nvarchar( max ),@detailAgeStr nvarchar( max ), @detailRowCount  int =1,@tmpFlag  int =1,@newMainId  int =0 as begin insert  into  TestProBatch_Main(MainName)  values (@mainName)  select  @newMainId=@@IDENTITY set  @detailRowCount=len(@detailNameStr)-len( replace (@detailNameStr, '|' , '' ))+1 set  @detailNameStr=@detailNameStr+ '|' set  @detailAgeStr=@detailAgeStr+ '|' while(@tmpFlag<=@detailRowCount) begin insert  into  TestProcBatch_Detail(MainId,DetailName,DetailAge)  values (@newMainId,dbo.F_RtnStrBySplitIndex(@detailNameStr,@tmpFlag),dbo.F_RtnStrBySplitIndex(@detailAgeStr,@tmpFlag)) set  @tmpFlag=@tmpFlag+1 end end

 这个例子是插入一条主单信息和对应的多条子信息，下面是两张表

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

--主表 CREATE  TABLE  [dbo].[TestProBatch_Main](      [ID] [ int ] IDENTITY(1,1)  NOT  NULL  primary  key ,      [MainName] [nvarchar](50)  NOT  NULL ,      [CreateTime] [datetime]  NOT  NULL      )；   --子表 CREATE  TABLE  [dbo].[TestProcBatch_Detail](      [ID] [ int ] IDENTITY(1,1)  NOT  NULL  primary  key ,      [MainId] [ int ]  NOT  NULL ,      [DetailName] [nvarchar](50)  NOT  NULL ,      [DetailAge] [ int ]  NOT  NULL ,      [CreateTime] [datetime]  NOT  NULL      )；

 dbo.F_RtnStrBySplitIndex是自定义的标量值函数，用于返回第几个分割符对应的字符串，如dbo.F_RtnStrBySplitIndex('jack|lilei|tom|nike',3) 则返回tom

下面是函数的创建

1 2 3 4 5 6 7 8 9 10 11 12

create  function  [dbo].[F_RtnStrBySplitIndex](@procStr nvarchar( max ),@splitStrIdx  int ) returns  nvarchar(250) as begin declare  @rtnStr nvarchar(250) declare  @currentSplitIdx  int declare  @preSplitIdx  int set  @currentSplitIdx=dbo.F_RtnSomeCharIdxInStrByNo( '|' ,@procStr,@splitStrIdx) set  @preSplitIdx=dbo.F_RtnSomeCharIdxInStrByNo( '|' ,@procStr,@splitStrIdx-1) set  @rtnStr= SUBSTRING (@procStr,@preSplitIdx+1,@currentSplitIdx-@preSplitIdx-1) return  @rtnStr end

 这个函数当中又用到了另一个函数dbo.F_RtnSomeCharIdxInStrByNo，用于返回某个字符在一个字符串的位置，下面是该函数的定义：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

ALTER  function  [dbo].[F_RtnSomeCharIdxInStrByNo](@findSplitStr  varchar (250), @procStr  varchar (8000), @n  smallint )      returns  int as begin      if @n < 1  return  (0)      declare  @start  smallint , @ count  smallint , @ index  smallint , @len  smallint      set  @ index  = charindex(@findSplitStr, @procStr)      if @ index  = 0  return  (0)      else  select  @ count  = 1, @len = len(@findSplitStr)      while @ index  > 0  and  @ count  < @n          begin              set  @start = @ index  + @len              select  @ index  = charindex(@findSplitStr, @procStr, @start), @ count  = @ count  + 1          end      if @ count  < @n  set  @ index  = 0      return  (@ index ) end

 调用存储过程：exec  Proc_TestBatchMainDetailIns 'mainName1','jack|lilei|tom|nike','20|18|22|17'

下面是成功插入后查询到的结果：