Solution for slow or missing data insertion for SQL Server large data _mssql

My device Inserts 2000 data per second into the database, 2 devices total 4,000, when inserted directly in the program with INSERT statement, the two devices at the same time insert about about 2,800, data loss about 1200, testing a lot of methods, The solution of two kinds of obvious effect is put out:

Method One: Use SQL Server functions:

1. Combine data into strings, use functions to insert data into the memory table, and then copy the memory table data to the table you want to insert.

2. Character change format: ' 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 ', in the middle of each row of data, ";" Separate, with "|" Between each field Separated.

3. Writing functions:

CREATE FUNCTION [dbo]. [Fun_funcname]  (@str VARCHAR (max), @splitchar char (1), @splitchar2 char (1))--Define return table RETURNS @t (MaxValue float,phase int,slopevalue Float,data varchar, Alarm Int,almlev int,gpstime datetime,updatetime datetime) AS/* Author:hejun Li Creat E date:2014-06-09/BEGIN DECLARE @substr VARCHAR (max), @substr2 VARCHAR (max)--Declare a single receive value 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)--Gets the number of delimiters IF @j=0 BEGIN--insert into @t VALUES (@substr, 1)--Inserts the entire string set @substr2 without a delimiter =@s
   UBSTR; Set @ii =0 set @jj =len (REPLACE (@substr2, @splitchar2, REPLICATE (@splitchar2, 2))-len (@substr2)--Get the number of delimiters while @ii < = @jj BEGIN IF (@ii < @jj) BEGIN SET @mm =charindex (@splitcHAR2, @substr2)-1--Gets the previous position of the delimiter 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 (@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))--Remove the obtained split string, and get the strings end else that still need to be split BEGIN-inserts data into the table insert into @t values (@MaxValue, @Phase, @SlopeValue, ' + @Dat when looping to the last value A + ', @Alarm, @AlmLev, cast (@substr2 as DateTime), GETDATE ()) End--end SET @ii = @ii +1 end ELSE BEGIN Set @i=0 while @i<=@j begin IF (@i<@j) begin SET @m=charindex (@splitchar, @substr)-1--Gets the first of the delimiter
  Position--insert into @t VALUES (left (@substr, @m), @i+1)-----Two cycles begin--1. Line gets the string set @substr2 = (left (@substr, @m) to intercept two times; --2. Initializes the starting position of the two intercept set @ii = 0--3. Gets the number of delimiters set @jj =len (REPLACE (@substr2, @splitchar2, REPLICATE (@splitchar2, 2))-len (@sub STR2)--Gets the number of delimiters while @ii <= @jj begin if (@ii < @jj) begin SET @mm =charindex (@splitchar2, @s UBSTR2)-1--Gets the previous position of the delimiter 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 (LEF T (@substr2, @mm) as float) else if (@ii =3) set @Data =cast (left (@substr2, @mm) as varchar) ELS E if (@ii =4) sET @Alarm =cast (@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))--Removes the obtained split string, and gets the strings that need to continue to split End Else BEGIN-inserts data into the table insert into @t values (@MaxValue, @Phase, when looping to the last value @SlopeValu E, ' + @Data + ', @Alarm, @AlmLev, cast (@substr2 as DateTime), GETDATE ()) End--end SET @ii = @ii +1-end-----two times Loop End SET @substr =right (@substr, LEN (@substr)-(@m+1))--Remove the obtained split string, and get the string ending else BEGIN--insert in
  To @t VALUES (@substr, @i+1)--the last split string is handled separately-----two cycles begin--1. The string gets two times to intercept the set @substr2 = @substr; --2. Initializes the starting position of the two intercept set @ii = 0--3. Gets the number of delimiters set @jj =len (REPLACE (@substr2, @splitchar2, REPLICATE (@splitchar2, 2))-len (@sub STR2)--Gets the number of delimiters while @ii <= @jj BegiN if (@ii < @jj) begin SET @mm =charindex (@splitchar2, @substr2)-1--Gets the previous position of the delimiter if (@ii =0) Set @MaxValue =cast (@substr2, @mm) as float) else if (@ii =1) set @Phase =cast left (@sub STR2, @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 (LEF T (@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))--Removes the obtained split string, gets the string end else BEGIN that needs to be split -Inserts data into the table insert into @t values when looping to the last value (@MaxValue, @Phase, @SlopeValue, ' + @Data + ', @Alarm, @AlmLev, cast (@su Bstr2 As DateTime), GETDATE ()) End Set @ii = @ii +1 ends-----Two cycles ending set @i=@i+1 

 End

4. Call Function statement:

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. Results show:

SELECT * from [MyTable];

Method Two: Use bulk INSERT

Large amount of data insert the first operation, using bulk to insert file data into the database

SQL code

Creating a Database

CREATE DATABASE [db_mgr] Go 
 

To create a test table

Use db_mgr 
CREATE TABLE dbo. T_student ( 
  f_id [int] IDENTITY (1,1) not NULL, 
  f_code varchar (a), 
  f_name varchar (MB), 
  F_memo nvarchar (+), 
  f_memo2 ntext, 
  PRIMARY KEY (f_id) 
) 
go

Populating test data

Insert into T_student (F_code, F_name, F_memo, F_memo2) Select
' code001 ', ' name001 ', ' memo001 ', ' remark ' UNION ALL select
    ' code002 ', ' name002 ', ' memo002 ', ' remark ' union ALL select
' code003 ', ' name003 ', ' memo003 ', ' remark ' union ALL select
' code004 ', ' name004 ', ' memo004 ', ' Memo ' union ALL select
' code005 ', ' name005 ', ' memo005 ', ' Memo ' union ALL select
' Code 006 ', ' name006 ', ' memo006 ', ' remark '

Open the xp_cmdshell stored procedure (with security hidden after opening)

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

To export a format file using bcp:

EXEC Master.. xp_cmdshell ' BCP db_mgr.dbo.T_Student format nul-f c:/student_fmt.xml-x-c-t '

To export a data file by using bcp:

EXEC Master.. xp_cmdshell ' BCP db_mgr.dbo.T_Student out c:/student.data-f c:/student_fmt.xml-t '

Empty data in a table

TRUNCATE TABLE Db_mgr.dbo.T_Student

To bulk import a data file using the BULK INSERT statement:

BULK INSERT db_mgr.dbo.T_Student from 
' C:/student.data '
with
( 
  formatfile = ' C:/student_fmt.xml '
)

Examples of using OPENROWSET (BULK):

T_student table must already exist

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

Examples of using OPENROWSET (BULK):

TT table can not exist

 select F_code, f_name into Db_mgr.dbo.tt from OPENROWSET (BULK N ' C:/student.data ', FORMAT File=n ' C:/student_fmt.xml ') as New_table_name