Workaround for SQL Server Big data insertion slow or lost data

My device on the 2000 data per second into the database, 2 devices a total of 4,000, when inserted in the program directly with the INSERT statement, the two devices at the same time to insert about a total of about 2,800, data loss about 1200 or so, testing a lot of methods, Two obvious solutions are put in effect:

Method One: Use SQL Server functions:

1. Combine the data into a string, use a function to insert the data into the memory table, and then copy the memory table data to the table you want to insert.

2. Combination of characters in the 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 ', each row of data in the middle with ";" Separated by a "|" Between each field Separated.

3. Write the function:

CREATE FUNCTION [dbo]. [Fun_funcname] (@str VARCHAR (max), @splitchar char (1), @splitchar2 char (1))--Defines the return table RETURNS @t table (MaxValue float,phase Int,slopevalue Float,data varchar (+), Alarm Int,almlev int,gpstime datetime,updatetime datetime) AS/* Author:hejun li Create dat e:2014-06-09 */BEGIN DECLARE @substr varchar (max), @substr2 varchar (max)-declares 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,@i i int, @jj int, @ijj1 int, @ijj2 int,@m int, @mm intset @j=len (REPLACE (@str, @splitchar, REPLICATE (@splitchar, 2)))-len (@str   )--Get the number of delimiters if @j=0 BEGIN--insert into @t VALUES (@substr, 1)--no delimiters insert the entire string set @substr2 = @substr; Set @ii =0 set @jj =len (REPLACE (@substr2, @splitchar2, REPLICATE (@splitchar2, 2)))-len (@substr2)--Gets the number of delimiters while @ii <= @jj BEGIN if (@ii < @jj) begin SET @mm =charindex (@splitchar2, @substr2)-1--Gets the front of the separator One place              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 (@s              UBSTR2, @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 (@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 string that still needs to be split end else BEGIN--inserts data into the table when looping to the last value INSERT into @t VALUES (@MaxValue, @Phase, @SlopeValue, ' + @Data + ', @Alarm, @AlmLev, cast (@substr2 as DateTime), GET  DATE ()) END      --end Set @ii = @ii +1 end end ELSE begin SET @i=0 while @i<=@j begin IF (@i<@j) BEG In SET @m=charindex (@splitchar, @substr)-1--Gets the previous position of the delimiter--insert into @t VALUES (left (@substr, @m), @i+1)-----Two cycles Start-  -1. Line gets the string set @substr2 = (left (@substr, @m)) to intercept for two times; --2. Initialize 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 (@ SUBSTR2)--Gets the number of delimiters while @ii <= @jj begin if (@ii < @jj) begin SET @mm =charindex (@splitchar2, @sub             STR2)-1--Gets the previous position of the separator 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 (@subst             R2, @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) inserts into @t VALUES (@MaxValue, @Phase, @SlopeValue, ' +@d Ata+ ", @Alarm, @AlmLev, cast (@substr2 as DateTime), GETDATE ()) SET @substr2 =right (@substr2, LEN (@substr2)-(@mm + 1))-- Remove the fetched split string and get the strings that still need to be split end else BEGIN-inserts data into the table when looping to the last value insert into @t values (@MaxV Alue, @Phase, @SlopeValue, ' + @Data + ', @Alarm, @AlmLev, cast (@substr2 as DateTime), GETDATE ()) END--end SET @ii =@i I+1 End-----Two loop end SET @substr =right (@substr, LEN (@substr)-(@m+1))--Removes the captured split string and obtains the strings that continue to be split end else BEGIN-  -insert into @t VALUES (@substr, @i+1)-separate processing of the last split string-----Two cycles to start--1. String Set @substr2 = @substr for two interception of strings; --2. Initialize 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 (@ SUBSTR2)--Gets the number of delimiters while @ii <= @jj begin if (@ii < @jj) begin SET @mm =charindex (@splitchar2, @sub    STR2)-1--Gets the previous position of the separator 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))--Removes the captured split string and obtains the strings that continue to be split end else BEGIN Insert data into the table when looping to the last value inserts into @t values (@MaxValue, @Phase, @SlopeValue, ' + @Data + ', @Alarm, @AlmLev, cast (@subs TR2 as DateTime), GETDATE ()) End Set @ii = @ii +1 End-----Two cycle end end set @i=@i+1 end end RETURN End

4. Call the 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 data volumes insert the first operation, using bulk to insert file data into the database

SQL code

Create a database

CREATE DATABASE [Db_mgr]go

Create a test table

Use Db_mgrcreate TABLE dbo. T_student (  f_id [int] IDENTITY () not NULL,  f_code varchar,  f_name varchar,  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 ', ' remarks ' union ALL select ' Code00  2 ', ' name002 ', ' memo002 ', ' Notes ' union ALL SELECT ' code003 ', ' name003 ', ' memo003 ', ' remarks ' union ALL select ' code004 ', ' name004 ', ' memo004 ', ' remarks ' union ALL select ' code005 ', ' name005 ', ' memo005 ', ' remarks ' union ALL select ' code006 ', ' name006 ', ' memo006 ', ' prepared Note

Open xp_cmdshell stored procedure (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 using bcp:

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

Emptying data in a table

TRUNCATE TABLE Db_mgr.dbo.T_Student

To bulk import data files using the BULK INSERT statement:

BULK INSERT Db_mgr.dbo.T_StudentFROM ' 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_namefrom 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.ttFROM OPENROWSET (BULK N ' c:/student.data ', formatfile=n ' c:/student_fmt.xml ') as New_table_name