使用事务和SqlBulkCopy批量导入数据

SQL⼤批量插⼊数据的⽅式（多表关联）.前段时间，在⼯作中遇到这个需求，需要⼤批量插⼊⼏万条甚⾄⼏⼗万的数据。

因为业务特殊，多张表的相互关联，通常做法是先往主表⾥⾯插⼊⼀条数据，然后获取主表的主键ID，再往其他关联的表⾥⾯插⼊ID的关联数据。

刚开始做的时候，想到⽤事务，把⼏万条SQL拼装起来，在⼀个事务⾥⾯去执⾏，结果很壮烈，执⾏性能⾮常糟糕。

⼏千条业务数据执⾏了⼏分钟。

⽤代码分析⼯具Dottrace⼀查，发现单单操作数据库的时间占了99.9%。

（Dottrace，代码性能分析⼯具，它分dottrace Performance和dottrace Memory两个⼯具，dottrace Performance⽤来分析代码性能，⽐如函数执⾏时间，调⽤次数，消耗时间⽐率等，dottrace Memory⼀般⽤来分析内存占⽤情况。

⼤家如果有兴趣的同学可以去下载玩下，对代码优化⼯作很有帮助的。

）⾔归正传，去⽹上搜了很多资料，原来2.0有⼀个新的特性：SqlBulkCopy，效率还是很⾼的。

然后结合⾃⼰的业务需求，修改了下代码。

现在跟⼤家⼀起来学习下。

（因为也是参考前辈写的资料，所以下⾯的⽰例都⼤同⼩异）建⽴测试数据库（BulkTestDB）、主表（BulkTestMain）、从表（BulkTestDetail）--Create DataBasecreate database BulkTestDB;gouse BulkTestDB;go--Create TableCreate table BulkTestMain(Id int primary key,GuidId long,--辅助的唯⼀标识Batch long,--导⼊的批次标识Name nvarchar(32)goCreate table BulkTestDetail(Id int primary key,PId int,Lesson nvarchar(32)go数据库建⽴完毕，开始编写后台代码View Codepublic void TestMain(){using (SqlConnection connection = new SqlConnection("你的链接字符串")){connection.Open();SqlTransaction transaction = connection.BeginTransaction("Transaction1");DataTable dtTestMain= GetTableSchema("BulkTestMain");//构建BulkTestMain表结构DataTable dtTestDetail = GetTableSchema("BulkTestDetail");//构建BulkTestDetail表结构Guid Batch = Guid.NewGuid();//插⼊的批次，为后⾯查询dtTestMainTmp 做条件for (int i = 0; i < 1000000; i++)//测试100w条数据{DataRow dr= dtTestMain.NewRow();Guid newGuid = Guid.NewGuid();dr["_GuidId"] = newGuid;dr["_Batch"] = Batch;dr["_UserName"] = "测试" + i.ToString();dtTestMain.Rows.Add(dr);for(int j = 0;j<10;j++)//给从表每次插⼊10条数据{DataRow dr1 = dtTestDetail.NewRow();dr1["_GuidId"]= newGuid;dr1["_Lesson"]="课程"+j.ToString();dtTestDetail.Rows.Add(dr1);}//这样做的⽬的，让主表与从表可以临时通过GuidId关联起来}BulkToDB(dtTestMain, "BulkTestMain", connection, transaction);//先让BulkTestMain插⼊了⼤量的数据，注意这些数据是临时的，在SqlTransaction提交之前，查询时要⽤with(nolock) DataSet dtTestMainTmp = GetNewImportData(Batch.ToString());//好吧，我们来查询下，刚才⼤量插⼊的10w条数据，这⾥只需要查询标识的2列字段Dictionary<string, long> dicGuidToID = new Dictionary<string, long>();foreach (DataRow dr in dtTestMainTmp.Tables[0].Rows){dicGuidToID.Add(dr[1].ToString(), Convert.ToInt64(dr[0]));}//dicGuidToID：guid字段与插⼊的主表ID字段关联起来成字典，⽤字典是为了访问起来效率（为什么获取字典key的值效率很⾼，有兴趣的可以去研究“散列表”的概念）foreach (DataRow dr in dtTestDetail.Rows)//现在给dtTestDetail的PId字段赋值(PId字段与主表Id外键关联){dr["_PId"] = dicGuidToID[dr["_GuidId"].ToString()].ToString();}dtTestDetail.Columns.Remove("_GuidId");//移除dtTestDetail的GuidId字段，使它与数据库列匹配BulkToDB(dtTestDetail,"BulkTestDetail",connection, transaction);//给从表插⼊数据mit();connection.Close();}}///<summary>///根据批次Batch获取导进来的临时数据///</summary>///<returns></returns>public static DataSet GetNewImportData(string batch){StringBuilder strSql = new StringBuilder();strSql.Append("SELECT [Id],[GuidId]").Append(" FROM ContactInfo WITH (NOLOCK) WHERE Batch=@batch");SqlParameter[] parameters = {new SqlParameter("@Batch", SqlDbType.BigInt){Value = batch}};DataSet ds = SqlHelper.ExecuteDataset(strSql.ToString(), parameters);return ds;}public static void BulkToDB(DataTable dtSource, string TableName,SqlConnection connection, SqlTransaction transaction) {using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.KeepIdentity, transaction)){sqlBulkCopy.DestinationTableName = TableName;//要插⼊数据的表的名称sqlBulkCopy.BatchSize = dtSource.Rows.Count;//数据的⾏数List<SqlBulkCopyColumnMapping> mpList = getMapping(TableName);//获取表映射关系foreach (SqlBulkCopyColumnMapping mp in mpList){sqlBulkCopy.ColumnMappings.Add(mp);}if (dtSource != null && dtSource.Rows.Count != 0){sqlBulkCopy.WriteToServer(dtSource);//插⼊数据}}}public static List<SqlBulkCopyColumnMapping> getMapping(string TableName){List<SqlBulkCopyColumnMapping> mpList = new List<SqlBulkCopyColumnMapping>();switch(TableName){case"BulkTestMain":{mpList.Add(new SqlBulkCopyColumnMapping("_Id", "Id"));mpList.Add(new SqlBulkCopyColumnMapping("_GuidId", "GuidId"));mpList.Add(new SqlBulkCopyColumnMapping("_Batch","Batch"));mpList.Add(new SqlBulkCopyColumnMapping("_UserName", "UserName"));}break;case"BulkTestDetail":{mpList.Add(new SqlBulkCopyColumnMapping("_Id", "Id"));mpList.Add(new SqlBulkCopyColumnMapping("_PId", "PId"));mpList.Add(new SqlBulkCopyColumnMapping("_Lesson", "Lesson"));}break;}return mpList;}private static DataTable GetTableSchema(string TableName){DataTable dataTable = new DataTable();switch(TableName){case"BulkTestMain" :{dataTable.Columns.AddRange(new DataColumn[] {new DataColumn("_Id",typeof(Int32)),new DataColumn("_GuidId",typeof(Int64)),new DataColumn("_Batch",typeof(Int64)),new DataColumn("_UserName",typeof(String))});}break;case"BulkTestDetail":{dataTable.Columns.AddRange(new DataColumn[] {new DataColumn("_Id",typeof(Int32)),new DataColumn("_PId",typeof(Int32)),new DataColumn("_GuidId",typeof(Int64)),new DataColumn("_Lesson",typeof(String))});}break;}return dataTable;}总算把代码copy完了，任务完成。

SQL Server批量插入数据案例详解这篇文章主要介绍了SQL Server批量插入数据案例详解,本篇文章通过简要的案例,讲解了该项技术的了解与使用,以下就是详细内容,需要的朋友可以参考下在SQL Server 中插入一条数据使用Insert语句，但是如果想要批量插入一堆数据的话，循环使用Insert不仅效率低，而且会导致SQL一系统性能问题。

下面介绍SQL Server支持的两种批量数据插入方法：Bulk和表值参数(Table-Valued Parameters)，高效插入数据。

新建数据库：--Create DataBasecreate database BulkTestDB;gouse BulkTestDB;go--Create TableCreate table BulkTestTable(Id int primary key,UserName nvarchar(32),Pwd varchar(16))go一.传统的INSERT方式先看下传统的INSERT方式：一条一条的插入（性能消耗越来越大，速度越来越慢）//使用简单的Insert方法一条条插入 [慢]#region [ simpleInsert ]static void simpleInsert(){Console.WriteLine("使用简单的Insert方法一条条插入");Stopwatch sw = new Stopwatch();SqlConnection sqlconn = new SqlConnection("server=.;database=BulkTestDB;user=sa;password=123456;");SqlCommand sqlcmd = new SqlCommand();mandText = string.Format("insert into BulkTestTable(Id,UserName,Pwd)values(@p0,@p1,@p2)");sqlcmd.Parameters.Add("@p0", SqlDbType.Int);sqlcmd.Parameters.Add("@p1", SqlDbType.NVarChar);sqlcmd.Parameters.Add("@p2", SqlDbType.NVarChar);mandType = CommandType.Text;sqlcmd.Connection = sqlconn;sqlconn.Open();try{//循环插入1000条数据，每次插入100条，插入10次。

sql server批量插入数据insert into写法**sql server批量插入数据****insert into写法**在SQL Server中，批量插入数据可以提高插入数据的效率，同时减少网络传输的数据量，从而加快数据处理的速度。

下面我将详细介绍如何使用insert into 写法进行批量插入数据。

一、准备工作在进行批量插入数据之前，你需要确保你的SQL Server数据库已经正确安装并配置好。

同时，你需要准备好要插入的数据，最好将其整理成适合批量插入的格式，例如CSV文件。

二、使用bulk insert语句SQL Server提供了一种名为bulk insert的语句，可以用于批量插入数据。

bulk insert语句可以将数据从文本文件（如CSV文件）中导入到数据库表中。

以下是bulk insert语句的基本语法：```sqlBULK INSERT table_name FROM 'path_to_file' WITH (FIELDTERMINATOR = 'char', ROWTERMINATOR = 'char', FIRSTROW = 'number')```其中，table_name是你想要插入数据的表名，path_to_file是包含数据的文本文件的路径，char是你定义字段分隔符和行分隔符的字符（通常是逗号），number是你指定第一行的行号（通常是1）。

三、批量插入数据下面是一个批量插入数据的示例：1. 将数据整理成CSV文件，例如data.csv。

2. 打开SQL Server Management Studio（SSMS）并连接到你的数据库。

3. 创建一个新的查询窗口或打开一个现有的查询窗口。

4. 输入以下SQL语句：```sqlBULK INSERT mytable FROM 'data.csv' WITH (FIELDTERMINATOR =',' ,ROWTERMINATOR = '\n',FIRSTROW = 2)```其中，mytable是你想要插入数据的表名。

mysql的批量更新：MySqlConnector.MySqlBulkCopy，修改数
据库⽂件位置
使⽤的是MySqlConnector组件，不是官⽅的MySqlData
cnstr = ”server=192.168.1.100;database=test;uid=root;pwd=s123;AllowLoadLocalInfile=true”
上述连接串中要加AllowLoadLocalInfile=true，否则会不⽀持，若仍不⾏则再加Local=true
MySqlConnector.MySqlConnection cn = new MySqlConnector.MySqlConnection(cnstr);
cn.Open();
MySqlConnector.MySqlBulkCopy p = new MySqlConnector.MySqlBulkCopy(cn);
p.DestinationTableName = "t1";
p.WriteToServer(dv.Table);
默认安装在程序⽬录下的Data，调整⽅法：
1. 停⽌服务
2. 复制Data⽬录到⽬标位置（不需要my.ini这个⽂件），并配置权限。

MariaDB服务的登录⽤户是NT SERVICE\MariaDB，直接复制这个并配置到新Data⽬录的权限上，允许完全控制，实际是计算机管理中并没有这个⽤户MariaDB
3. 修改原data/my.ini中的datadir⽬录到新的位置，并删除除my.ini外的所有⽂件。

注意保存my.ini⽂件是以ansi格式保存
4. 重启服务。

sqlbulkcopy 对索引的影响及解决方法SQLBulkCopy是用于将大量数据批量插入到SQL Server数据库中的类。

它是一种高性能的数据加载方法，可以显著提高数据插入的效率。

但是，当使用SQLBulkCopy插入大量数据时，可能会对索引产生一定的影响。

SQL索引是一种数据库对象，用于加快对数据库表中数据的查询和检索操作。

它可以通过创建适当的数据结构来提高数据库查询的性能。

然而，由于SQLBulkCopy是通过批量插入数据来工作的，它在插入数据时可能会对已有的索引产生影响，从而降低数据库的性能。

当使用SQLBulkCopy进行批量插入时，影响索引的主要问题有两个方面：1. 索引更新2. 索引重建首先，SQLBulkCopy会对已有的索引进行更新。

当插入新的数据时，数据库引擎需要检查已有的索引，以确保插入的数据与索引的约束相符。

这个过程可能会引起索引的更新，从而影响插入操作的性能。

其次，当使用SQLBulkCopy插入大量数据时，索引可能会达到一定的阈值，需要进行重建。

索引重建是一个耗时的操作，可能会对数据库的性能产生较大的影响。

为了解决SQLBulkCopy对索引的影响，我们可以考虑以下几种方法：1. 关闭索引可以在使用SQLBulkCopy进行数据插入之前，暂时关闭索引。

这样可以减少索引更新和重建的开销，提高插入数据的性能。

待插入操作完成后，再重新打开索引。

2. 批量导入到临时表可以先将需要插入的数据批量导入到一个临时表中，然后再使用SQL语句将临时表中的数据插入到目标表中。

这样可以避免直接对目标表进行大量的插入操作，减少对索引的影响。

3. 分批次插入可以将需要插入的数据分成多个批次进行插入操作，而不是一次性插入所有数据。

这样可以减少单次插入的数据量，降低对索引的更新和重建的影响。

4. 调整索引设置可以根据具体的情况对索引进行优化调整。

例如，可以考虑使用非聚集索引代替聚集索引，或者调整聚集索引的填充因子等。

java sqlserverbulkcopy用法-回复Java SQLServerBulkCopy用法SQLServerBulkCopy是一个用于将数据从一个源数据表复制到目标数据表的高效工具。

在Java中使用SQLServerBulkCopy需要引入sqljdbc4.jar驱动包。

1. 导入驱动包在Java项目中，首先需要导入sqljdbc4.jar驱动包。

你可以在Maven中添加以下依赖项：xml<dependency><groupId>com.microsoft.sqlserver</groupId><artifactId>sqljdbc4</artifactId><version>4.0</version></dependency>2. 创建连接在使用SQLServerBulkCopy之前，我们需要先创建一个到SQL Server 数据库的连接。

可以使用`DriverManager.getConnection()`方法创建一个数据库连接对象。

javaimport java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;public class SqlServerBulkCopyExample {public static void main(String[] args) {String connectionUrl ="jdbc:sqlserver:localhost:1433;databaseName=your_database_nam e;user=your_username;password=your_password;";try (Connection connection =DriverManager.getConnection(connectionUrl)) {在这里执行SQLServerBulkCopy的相关操作} catch (SQLException e) {e.printStackTrace();}}}请注意，上述代码中的连接字符串`connectionUrl`应该通过替换相应数据库的详细信息，如数据库名称、用户名和密码等。

oracle下的sqlldr命令使⽤⽅法（sqlldr⼯具的使⽤（批量导⼊数据））sqlldr这个命令可以将⽂本中的数据⼤批量的导⼊到oracle数据库表中在win7下使⽤很简单，⾃⼰亲⾃动⼿试了下，分享给⼤家1.建⽴⼀个简单的⽂本⽂件，⽐如：testLoad.txt，内容如下：1,"test",332,"test",333,"test",334,"test",335,"test",336,"test",337,"test",338,"test",339,"test",3310,"test",332.在数据库中建⽴⼀个表，⽐如：create table emp(id number(5),name varchar2(20),age number(3),constraint pk_id primary key(id));3.建⽴⼀个简单的控制⽂件，⽐如：test.ctl，内容如下：load data--控制⽂件标识infile 'f:\testload.txt'--要输⼊的数据⽂件名appendinto table test.emp--test为⽤户名，emp为表名--向哪个表追加记录fields terminated by ','--指定分割符，终⽌字段值( id ,name ,age)4.打开cmdd命令⾏⼯具，sqlplus登录到相应的数据库，输⼊如下命令sqlldr control='f:\test.ctl' data='f:\testLoad.txt'执⾏结果显⽰记录数5.如果想记录执⾏的过程和⽇志的话，可以这么写;sqlldr control='f:\test.ctl' data='f:\testLoad.txt' log='f:\log.log'6． sqlldr⽤到的主要参数1) userid -- ORACLE username/password --数据库⽤户名/密码2）control –控制⽂件3) log –记录的⽇志⽂件4) bad –坏数据⽂件，记录错误的未加载数据5) data –数据⽂件,* data参数只能指定⼀个数据⽂件,如果控制⽂件也通过infile指定了数据⽂件,并且指定多个,则sqlldr在执⾏时,先加载data参数指定的数据⽂件,控制⽂件中第⼀个infile指定的数据⽂件被忽略,但后续的infile指定的数据⽂件继续有效6) discard –丢弃的数据⽂件7) discardmax –允许丢弃数据的最⼤值 (默认全部)8) skip --跳过记录数,从数据⽂件中,从第⼀⾏开始要计算要跳过的⾏数 (默认0)9) load -- Number of logical records to load (默认全部)10) errors –允许的错误记录数,超过则终⽌任务(默认50)11) rows -- Number of rows in conventional path bind array or between direct path data saves（每次提交的记录数，默认:常规路径64,直接路径全部，所以使⽤直接路径的话，效率会⽐普通的好太多太多）12) bindsize -- Size of conventional path bind array in bytes（每次提交记录的缓冲区的⼤⼩，字节为单位，默认256000)13) silent --禁⽌输出信息(header,feedback,errors,discards,partitions)14) direct –使⽤直通路径⽅式导⼊(默认FALSE)如果表中有索引的话，是不能指定direct=TRUE的，除⾮使⽤skip_index_maintenance=TRUE，这个就是在导⼊的时候忽略索引，所以在数据导⼊完毕以后，查看索引的状态应该都是⽆效的，需要重建之,如下SQL：select * from dba_indexes where table_name='?' ;alter idnex index_name rebuild ;重新建⽴索引要⽐新建索引快。

c#⼏种数据库的⼤数据批量插⼊（SqlServer、Oracle、SQLite和MySql）这篇⽂章主要介绍了c#⼏种数据库的⼤数据批量插⼊（SqlServer、Oracle、SQLite和MySql），需要的朋友可以了解⼀下。

在之前只知道SqlServer⽀持数据批量插⼊，殊不知道Oracle、SQLite和MySql也是⽀持的，不过Oracle需要使⽤Orace.DataAccess驱动，今天就贴出⼏种数据库的批量插⼊解决⽅法。

⾸先说⼀下，IProvider⾥有⼀个⽤于实现批量插⼊的插件服务接⼝IBatcherProvider，此接⼝在前⼀篇⽂章中已经提到过了。

///<summary>///提供数据批量处理的⽅法。

///</summary>public interface IBatcherProvider : IProviderService{///<summary>///将<see cref="DataTable"/>的数据批量插⼊到数据库中。

///</summary>///<param name="dataTable">要批量插⼊的<see cref="DataTable"/>。

</param>///<param name="batchSize">每批次写⼊的数据量。

</param>void Insert(DataTable dataTable, int batchSize = 10000);}⼀、SqlServer数据批量插⼊SqlServer的批量插⼊很简单，使⽤SqlBulkCopy就可以，以下是该类的实现：///<summary>///为 System.Data.SqlClient 提供的⽤于批量操作的⽅法。

数据库批量导入导出的方法与性能优化数据库的批量导入导出操作是在日常开发和运维工作中非常常见的任务。

合理优化这些操作可以显著提高数据库的性能和效率。

本文将介绍几种常见的数据库批量导入导出方法，并探讨如何进行性能优化，以提高导入导出的速度和效率。

一、数据库批量导入方法1. 使用SQL语句的LOAD DATA INFILE命令LOAD DATA INFILE命令是一种高效的数据库批量导入方法，它能够快速导入大量数据。

使用LOAD DATA INFILE命令时，需要将数据存储在文本文件中，并通过指定正确的分隔符和行结束符来确保数据能正确导入。

2. 使用INSERT语句的多值插入当需要将大量数据导入数据库表中时，可以使用INSERT语句的多值插入方法。

这种方法只需要执行一条INSERT语句，每次插入多行数据。

与循环插入单条数据相比，使用多值插入方法可以减少数据库操作的次数，提高导入速度。

3. 使用数据库特定的工具或API许多数据库提供了专门的工具或API，用于数据库批量导入。

这些工具可以根据数据库的特性和要求，在性能和功能上做出优化。

例如，MySQL提供了mysqlimport工具，能够高效地导入CSV或文本文件。

二、数据库批量导出方法1. 使用SELECT INTO OUTFILE命令SELECT INTO OUTFILE命令是一种常用的数据库批量导出方法，它将查询结果导出为文本文件。

使用该命令时需要注意文件路径和用户权限问题。

SELECT INTO OUTFILE命令支持导出多个表的数据，查询结果可以以CSV、XML或SQL等格式导出。

2. 使用数据库特定的工具或API类似数据库批量导入方法，许多数据库还提供了专门的工具或API，用于数据库批量导出。

这些工具可以根据数据库的特性和要求，在性能和功能上做出优化。

例如，MySQL提供了mysqldump工具，能够高效地导出数据库表的结构和数据。

三、性能优化技巧1. 选择合适的导入导出方法根据数据量和导入导出的频率，选择合适的导入导出方法非常重要。

MySQL中的批量操作和事务提交介绍MySQL是一个开源的数据管理系统，用于管理和操作关系型数据库。

在实际应用中，我们经常需要对数据库进行批量操作，以提高效率和性能。

批量操作指的是在一次请求中同时操作多个数据库对象，如插入、更新和删除多条数据记录，而不是逐条进行操作。

同时，为了保持数据的一致性和可靠性，我们还需要使用事务提交来确保操作的原子性。

批量插入数据在MySQL中，批量插入数据是常见的操作。

当我们需要插入大量数据时，逐条插入效率很低，可以使用批量插入来提高性能。

使用INSERT INTO语句插入多条数据可以使用以下两种方法：方法一：使用多个VALUES子句INSERT INTO table_name (column1, column2, column3)VALUES (value1, value2, value3),(value4, value5, value6),...(valueN, valueN+1, valueN+2);这种方式需要手动书写多个VALUES子句，适用于已知数据量比较小的情况。

可以一次插入多行数据，但是VALUES子句较多时，书写和管理起来比较麻烦。

方法二：使用INSERT INTO ... SELECT语句INSERT INTO table_name (column1, column2, column3)SELECT value1, value2, value3UNION ALLSELECT value4, value5, value6UNION ALL...SELECT valueN, valueN+1, valueN+2;这种方式可以将多个SELECT语句合并到一起，通过UNION ALL关键字连接。

相比于方法一，可以在一个SELECT语句中一次性插入多行数据，减少了VALUES子句的书写和管理。

批量更新数据除了批量插入数据，我们还需要进行批量更新数据。

在MySQL中，可以使用UPDATE语句进行批量更新。