"Database MSSQL2008" uses table-valued parameters to quickly insert data

In the project, it is necessary to insert about 10W of data from the original file into the database, if the original SQL statement "INSERT into", whether it is a circular insert or BULK INSERT, is not inefficient or the system memory is exhausted, can not meet the demand.

Through the online review, found that MSSQL from 2008 onwards, the database supports the table-valued parameters of the bulk insert data, using this method, one-time insertion of 10W data is only 3-5 seconds of things, everything began to become beautiful ~

Steps and points to bulk insert data using table-valued Parameters:

1, the data source needs to be the DataTable form, and the field order must and the corresponding Database data table's field attribute, the order consistent

2. Sample Code

    idbconn.open ();     idbcmd.commandtext =  "insert into "  + tableName +  " select *  from @ " + tableName;    SqlParameter param =  ( SqlCommand) (Idbcmd)). Parameters.addwithvalue ("@"  + TABLENAME, DT);     param. Sqldbtype = sqldbtype.structured;        param. typename = paramtypename;    if  (dt != null &&  Dt. rows.count > 0)     {          Idbcmd.executescalar ();    }     ((SqlCommand) (Idbcmd)). Parameters.removeat ("@"  + tablename);     idbconn.close (); 

3. The @tablename in the sample is a table-valued variable name that needs to be identified independently if multiple representations are inserted using table-valued parameters, otherwise the table-valued variable already exists.

4, because the database connection in the project is only one, and Idbcmd is its member variable. If you do not remove the table-valued parameter from the last call to Parameters.remove, you will be prompted at the next call, "There are not enough fields in the structured type." The structured type must contain at least one field. Guess is because the next time the call, only the DataTable corresponding to the table-valued parameter, and the previously added table value data has not been assigned to the corresponding DataTable, then in the subsequent execution of the statement, command polling parameters found insufficient parameters, will prompt the above error.

This article is from "Qin Yang Feng" blog, please be sure to keep this source http://qyf0310.blog.51cto.com/1875017/1546238

"Database MSSQL2008" uses table-valued parameters to quickly insert data