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