SQL Server Large data volume insertion slow or data loss resolution

Problem Description: My device per second data inserted into the database,2 devices A total of 4000, when in the program directly with the insert When the statement is inserted, the two devices are inserted at the same time about a total of about 2800 , data loss about about Three, find a lot of solutions, Two obvious solutions are collated:

The first type: Use SQL Server function :

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 "; "Separate , 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,slopevalu     e float,data varchar (p), 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)-declares a single receive value DECLARE @MaxValue float, @Phase int, @SlopeValue float, @Data varchar (8000), @Alarm int, @AlmLev int, @GpsTime datetime SET @[email& Nbsp;protected] DECLARE @i int,@j int, @ii int, @jj int, @ijj1 int, @ijj2 int,@m int, @mm int SET @j=len (REPLACE (@str, @s Plitchar,replicate (@splitchar, 2))-len (@str)--Get the number of delimiters IF @j=0 BEGIN--insert into @t VALUES (@sub      str,1)--without a separator, insert the entire string set @[email protected];  Set @ii =0 set @jj =len (REPLACE (@substr2, @splitchar2, REPLICATE (@splitchar2, 2)))-len (@substr2)--Gets the number of delimiters while @ii <[email protected]beginif (@ii < @jj) bEginset @mm =charindex (@splitchar2, @substr2)-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 VALU ES (@MaxValue, @Phase, @SlopeValue, ' [email protected]+ ', @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 endelsebegin--insert data into the table when looping to the last value  INSERT into @t VALUES (@MaxValue, @Phase, @SlopeValue, ' [email protected]+ ', @Alarm, @AlmLev, cast (@substr2 as datetime), GETDATE ()) END--endset @[email protected]+1end end ELSE BEGIN SET @i=0 while @ I<[email protected] BEGIN IF(@i<@j)      BEGIN SET @m=charindex (@splitchar, @substr) 1--Gets the previous position of the separator--insert into @t VALUES (left (@substr, @m), @i+1)    -----Two cycles start--1. Line get two intercept string set @substr2 = (left (@substr, @m)); --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 (@s                    UBSTR2)--Get the number of delimiters while @ii <[email protected] begin if (@ii < @jj) begin                        SET @mm =charindex (@splitchar2, @substr2) 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 =cas T (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 (@substr2, @mm) as int) else if (@ii =6) INSERT into @t VALUES (@Max                    Value, @Phase, @SlopeValue, ' [email protected]+ ', @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--inserting data into a table when looping to the last value insert into @t values (@MaxValue, @Phase, @SlopeValue, "[Email protec    Ted]+ ", @Alarm, @AlmLev, cast (@substr2 as DateTime), GETDATE ()) END--end SET @[email protected]+1         End-----Two loop end SET @substr =right (@substr, LEN (@substr)-(@m+1))--Removes the captured split string and gets the strings that need to 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. Line gets the string set @[email& to intercept two times    Nbsp;protected]; --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 (@subst                    R2)--Get the number of delimiters while @ii <[email protected] begin if (@ii < @jj) begin Set @mm =charindex (@splitchar2, @substr2) 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 F                    Loat) 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, "[email protected]+", @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--inserting data into a table when looping to the last value insert into @t values (@MaxValue, @Phase, @SlopeValue, "[Email pro Tected]+ ", @Alarm, @AlmLev, cast (@substr2 as DateTime), GETDATE ()) End SET @[email protected]+1 End-----two times Loop end end SET @[email protected]+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];

The second type: Use BULK INSERT

-- Large data volume insert the first operation, using Bulk to insert file data into the database

--sql Code

-- Create a database

Create DATABASE [Db_mgr] GO--Creating a test table use db_mgr CREATE 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

-- filling test Data

Insert into T_student (F_code, F_name, F_memo, F_memo2) Select ' code001 ', ' name001 ', ' memo001 ', ' remarks ' union ALL select ' Code 002 ', ' name002 ', ' memo002 ', ' remarks ' union ALL select ' code003 ', ' name003 ', ' memo003 ', ' remarks ' union ALL select ' code004 ', ' NAME0 ', ' memo004 ', ' remarks ' union ALL select ' code005 ', ' name005 ', ' memo005 ', ' remarks ' union ALL select ' code006 ', ' name006 ', ' memo00 6 ', ' remarks '

--Openxp_cmdshellstored Procedures (security implications after opening)

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

-- use bcp to export format files:

-- use bcp to export the data file:

-- empty the data in the table

-- Bulk import of data files using the Bulk Insert statement:

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

Example of using OPENROWSET (BULK) :--The t_student table must already exist

--Using OPENROWSET (BULK) Example:--TT table can not exist