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

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 

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',';','|');

select * from [mytable] ;

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', '备注'

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

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

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 db_mgr.dbo.T_Student FROM 'C:/student.data'WITH(   FORMATFILE = 'C:/student_fmt.xml')

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

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