SQL Server stored procedure inserts data in bulk

In the system often encountered in the bulk of the data inserted into the database, stored procedures do not have an array, only through the string segmentation loop inserted, here is an example of my study:

CREATE proc [dbo]. [Proc_testbatchmaindetailins] @mainName nvarchar, @detailNameStr nvarchar (max), @detailAgeStr nvarchar (max), @ Detailrowcount int=1, @tmpFlag int=1, @newMainId int=0as begininsert into Testprobatch_main (mainname) VALUES (@mainName) Select @[email Protected] @IDENTITYset @detailRowCount =len (@detailNameStr)-len (replace (@detailNameStr, ' | ', ')) + 1set @[email protected]+ ' | ' Set @[email protected]+ ' | ' while (@tmpFlag <[email protected]) Begininsert into Testprocbatch_detail (mainid,detailname,detailage) VALUES (@ Newmainid,dbo. F_rtnstrbysplitindex (@detailNameStr, @tmpFlag), dbo. F_rtnstrbysplitindex (@detailAgeStr, @tmpFlag)) set @[email protected]+1endend

This example is to insert a single master message and the corresponding multi-note information, the following are two tables

--Main Table CREATE TABLE [dbo]. [Testprobatch_main] ([ID] [int] IDENTITY (n) not null primary key,[mainname] [nvarchar] (() not null,[createtime] [datetime] NOT NULL);--sub-table CR Eate TABLE [dbo]. [Testprocbatch_detail] ([ID] [int] IDENTITY (all) not NULL primary Key,[mainid] [int.] not null,[detailname] [nvarchar] (a) not null,[detailage] [i NT] not null,[createtime] [datetime] is not NULL);

Dbo. F_rtnstrbysplitindex is a custom scalar-valued function that returns the string corresponding to the first number of delimiters, such as dbo. F_rtnstrbysplitindex (' Jack|lilei|tom|nike ', 3) returns Tom

Here is the creation of the function

Create function [dbo]. [F_rtnstrbysplitindex] (@procStr nvarchar (max), @splitStrIdx int) returns nvarchar (+) asbegindeclare @rtnStr nvarchar (+) DECLARE @ Currentsplitidx intdeclare @preSplitIdx intset @currentSplitIdx =dbo. F_rtnsomecharidxinstrbyno (' | ', @procStr, @splitStrIdx) set @preSplitIdx =dbo. F_rtnsomecharidxinstrbyno (' | ', @procStr, @splitStrIdx-1) Set @rtnStr =substring (@procStr, @preSplitIdx +1,@[email Protected]) return @rtnStrend

Another function, dbo, is used in this function. F_rtnsomecharidxinstrbyno, which returns the position of a character in a string, the following is the definition of the function:

ALTER function [dbo]. [F_rtnsomecharidxinstrbyno] (@findSplitStr varchar, @procStr varchar (8000), @n smallint)    Returns Intasbegin    if @n < 1 return (0)    declare @start smallint, @count smallint, @index smallint, @len Smallin T    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 = Cha Rindex (@findSplitStr, @procStr, @start), @count = @count + 1        End    if @count < @n set @index = 0    return (@ Index) End

Call stored procedure: Exec proc_testbatchmaindetailins ' mainName1 ', ' jack|lilei|tom|nike ', ' 20|18|22|17 '

The following is the result of a successful insert query:

SQL Server stored procedure inserts data in bulk