当前位置:坤哥网-kungge-sqlserver存储过程批量插入数据

sqlserver存储过程批量插入数据

2015/7/29 0:01:00 坤哥网阅读(909) 评论(1)

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

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

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

--主表
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

下面是函数的创建

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,用于返回某个字符在一个字符串的位置,下面是该函数的定义:

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'

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

 

分类: SqlServer

有话要说? =>【不用注册,直接登录】,然后刷新本页面来发表您的观点(●'◡'●)