在系统中经常会遇到向数据库中批量插入数据情况，存储过程中没有数组，只有通过字符串分割循环插入，下面是一个本人研究的一个例子：
|
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'
下面是成功插入后查询到的结果：
