针对Sqlserver大数据量插入速度慢或丢失数据的解决方法

我的设备上每秒将2000条数据插入数据库，2个设备总共4000条，当在程序里面直接用insert语句插入时，两个设备同时插入大概总共能插入约2800条左右，数据丢失约1200条左右，测试了很多方法，整理出了两种效果比较明显的解决办法：

方法一：使用Sql Server函数:

1.将数据组合成字串，使用函数将数据插入内存表，后将内存表数据复制到要插入的表。

2.组合成的字符换格式： 111|222|333|456,7894,7458|0|1|2014-01-01 12:15:16;1111|2222|3333|456,7894,7458|0|1|2014-01-01 12:15:16 ,每行数据中间用“;”隔开，每个字段之间用“|”隔开。

3.编写函数：

CREATE FUNCTION [dbo].[fun_funcname](@str VARCHAR(max),@splitchar CHAR(1),@splitchar2 CHAR(1))
定义返回表
RETURNS @t TABLE(MaxValue float,Phase int,SlopeValue float,Data varchar(600),Alarm int,AlmLev int,GpsTime datetime,UpdateTime datetime) AS
/*
author:hejun li
create date:2014-06-09
*/
BEGIN
DECLARE @substr VARCHAR(max),@substr2 VARCHAR(max)
申明单个接收值
declare @MaxValue float,@Phase int,@SlopeValue float,@Data varchar(8000),@Alarm int,@AlmLev int,@GpsTime datetime
SET @substr=@str
DECLARE @i INT,@j INT,@ii INT,@jj INT,@ijj1 int,@ijj2 int,@m int,@mm int
SET @j=LEN(REPLACE(@str,@splitchar,REPLICATE(@splitchar,2)))-LEN(@str) 获取分割符个数
IF @j=0
BEGIN
INSERT INTO @t VALUES (@substr,1) 没有分割符则插入整个字串
set @substr2=@substr;
set @ii=0
SET @jj=LEN(REPLACE(@substr2,@splitchar2,REPLICATE(@splitchar2,2)))-LEN(@substr2) 获取分割符个数
WHILE @ii =@jj
BEGIN
if(@ii @jj)
begin
SET @mm=CHARINDEX(@splitchar2,@substr2)-1 获取分割符的前一位置
if(@ii=0)
set @MaxValue=cast(LEFT(@substr2,@mm) as float)
else if(@ii=1)
set @Phase=cast(LEFT(@substr2,@mm) as int)
else if(@ii=2)
set @SlopeValue=cast(LEFT(@substr2,@mm) as float)
else if(@ii=3)
set @Data=cast(LEFT(@substr2,@mm) as varchar)
else if(@ii=4)
set @Alarm=cast(LEFT(@substr2,@mm) as int)
else if(@ii=5)
set @AlmLev=cast(LEFT(@substr2,@mm) as int)
else if(@ii=6)
INSERT INTO @t VALUES(@MaxValue,@Phase,@SlopeValue, +@Data+ ,@Alarm,@AlmLev,cast(@substr2 as datetime),GETDATE())
SET @substr2=RIGHT(@substr2,LEN(@substr2)-(@mm+1)) 去除已获取的分割串，得到还需要继续分割的字符串
end
else
BEGIN
当循环到最后一个值时将数据插入表
INSERT INTO @t VALUES(@MaxValue,@Phase,@SlopeValue, +@Data+ ,@Alarm,@AlmLev,cast(@substr2 as datetime),GETDATE())
END
END
SET @ii=@ii+1
END
END
ELSE
BEGIN
SET @i=0
WHILE @i =@j
BEGIN
IF(@i @j)
BEGIN
SET @m=CHARINDEX(@splitchar,@substr)-1 获取分割符的前一位置
INSERT INTO @t VALUES(LEFT(@substr,@m),@i+1)
二次循环开始
1.线获取要二次截取的字串
set @substr2=(LEFT(@substr,@m));
2.初始化二次截取的起始位置
set @ii=0
3.获取分隔符个数
SET @jj=LEN(REPLACE(@substr2,@splitchar2,REPLICATE(@splitchar2,2)))-LEN(@substr2) 获取分割符个数
WHILE @ii =@jj
BEGIN
if(@ii @jj)
begin
SET @mm=CHARINDEX(@splitchar2,@substr2)-1 获取分割符的前一位置
if(@ii=0)
set @MaxValue=cast(LEFT(@substr2,@mm) as float)
else if(@ii=1)
set @Phase=cast(LEFT(@substr2,@mm) as int)
else if(@ii=2)
set @SlopeValue=cast(LEFT(@substr2,@mm) as float)
else if(@ii=3)
set @Data=cast(LEFT(@substr2,@mm) as varchar)
else if(@ii=4)
set @Alarm=cast(LEFT(@substr2,@mm) as int)
else if(@ii=5)
set @AlmLev=cast(LEFT(@substr2,@mm) as int)
else if(@ii=6)
INSERT INTO @t VALUES(@MaxValue,@Phase,@SlopeValue, +@Data+ ,@Alarm,@AlmLev,cast(@substr2 as datetime),GETDATE())
SET @substr2=RIGHT(@substr2,LEN(@substr2)-(@mm+1)) 去除已获取的分割串，得到还需要继续分割的字符串
end
else
BEGIN
当循环到最后一个值时将数据插入表
INSERT INTO @t VALUES(@MaxValue,@Phase,@SlopeValue, +@Data+ ,@Alarm,@AlmLev,cast(@substr2 as datetime),GETDATE())
END
END
SET @ii=@ii+1
END
二次循环结束
SET @substr=RIGHT(@substr,LEN(@substr)-(@m+1)) 去除已获取的分割串，得到还需要继续分割的字符串
END
ELSE
BEGIN
INSERT INTO @t VALUES(@substr,@i+1) 对最后一个被分割的串进行单独处理
二次循环开始
1.线获取要二次截取的字串
set @substr2=@substr;
2.初始化二次截取的起始位置
set @ii=0
3.获取分隔符个数
SET @jj=LEN(REPLACE(@substr2,@splitchar2,REPLICATE(@splitchar2,2)))-LEN(@substr2) 获取分割符个数
WHILE @ii =@jj
BEGIN
if(@ii @jj)
begin
SET @mm=CHARINDEX(@splitchar2,@substr2)-1 获取分割符的前一位置
if(@ii=0)
set @MaxValue=cast(LEFT(@substr2,@mm) as float)
else if(@ii=1)
set @Phase=cast(LEFT(@substr2,@mm) as int)
else if(@ii=2)
set @SlopeValue=cast(LEFT(@substr2,@mm) as float)
else if(@ii=3)
set @Data=cast(LEFT(@substr2,@mm) as varchar)
else if(@ii=4)
set @Alarm=cast(LEFT(@substr2,@mm) as int)
else if(@ii=5)
set @AlmLev=cast(LEFT(@substr2,@mm) as int)
else if(@ii=6)
INSERT INTO @t VALUES(@MaxValue,@Phase,@SlopeValue, +@Data+ ,@Alarm,@AlmLev,cast(@substr2 as datetime),GETDATE())
SET @substr2=RIGHT(@substr2,LEN(@substr2)-(@mm+1)) 去除已获取的分割串，得到还需要继续分割的字符串
end
else
BEGIN
当循环到最后一个值时将数据插入表
INSERT INTO @t VALUES(@MaxValue,@Phase,@SlopeValue, +@Data+ ,@Alarm,@AlmLev,cast(@substr2 as datetime),GETDATE())
END
SET @ii=@ii+1
END
二次循环结束
END
SET @i=@i+1
END
END
RETURN
END

4.调用函数语句：

insert into [mytable] select * from [dbo].[fun_funcname]( 111|222|333|456,7894,7458|0|1|2014-01-01 12:15:16;1111|2222|3333|456,7894,7458|0|1|2014-01-01 12:15:16 , , | );

5.结果展示：

select * from [mytable] ;

方法二：使用BULK INSERT

大数据量插入第一种操作，使用Bulk将文件数据插入数据库

Sql代码

创建数据库

CREATE DATABASE [db_mgr]
GO

创建测试表

USE db_mgr
CREATE TABLE dbo.T_Student(
F_ID [int] IDENTITY(1,1) NOT NULL,
F_Code varchar(10) ,
F_Name varchar(100) ,
F_Memo nvarchar(500) ,
F_Memo2 ntext ,
PRIMARY KEY (F_ID)
)
GO

填充测试数据

Insert Into T_Student(F_Code, F_Name, F_Memo, F_Memo2) select
code001 , name001 , memo001 , 备注 union all select
code002 , name002 , memo002 , 备注 union all select
code003 , name003 , memo003 , 备注 union all select
code004 , name004 , memo004 , 备注 union all select
code005 , name005 , memo005 , 备注 union all select
code006 , name006 , memo006 , 备注

开启xp_cmdshell存储过程（开启后有安全隐患）

EXEC sp_configure show advanced options , 1;
RECONFIGURE;EXEC sp_configure xp_cmdshell , 1;
EXEC sp_configure show advanced options , 0;
RECONFIGURE;

使用bcp导出格式文件：

EXEC master..xp_cmdshell BCP db_mgr.dbo.T_Student format nul -f C:/student_fmt.xml -x -c -T

使用bcp导出数据文件：

EXEC master..xp_cmdshell BCP db_mgr.dbo.T_Student out C:/student.data -f C:/student_fmt.xml -T

将表中数据清空

truncate table db_mgr.dbo.T_Student

使用Bulk Insert语句批量导入数据文件：

BULK INSERT db_mgr.dbo.T_Student
FROM C:/student.data
WITH
(
FORMATFILE = C:/student_fmt.xml

使用OPENROWSET(BULK)的例子：

T_Student表必须已存在

INSERT INTO db_mgr.dbo.T_Student(F_Code, F_Name) SELECT F_Code, F_Name
FROM OPENROWSET(BULK N C:/student.data , FORMATFILE=N C:/student_fmt.xml ) AS new_table_name

使用OPENROWSET(BULK)的例子：

tt表可以不存在

SELECT F_Code, F_Name INTO db_mgr.dbo.tt
FROM OPENROWSET(BULK N C:/student.data , FORMATFILE=N C:/student_fmt.xml ) AS new_table_name

我想要获取技术服务或软件
服务范围：MySQL、ORACLE、SQLSERVER、MongoDB、PostgreSQL 、程序问题
服务方式：远程服务、电话支持、现场服务，沟通指定方式服务
技术标签：数据恢复、安装配置、数据迁移、集群容灾、异常处理、其它问题

本站部分文章参考或来源于网络，如有侵权请联系站长。
数据库远程运维 针对Sqlserver大数据量插入速度慢或丢失数据的解决方法