本次在提供一个关于解决批量导入由于数据量过多,会报出参数过多,我们应该清楚一个问题,sqlserver导入数据时,允许的最大sql长度是3000个字符,但当我们数据过多,及时是使用batch结合事务处理已然会爆掉,实际上我们还可以用另一种方式,将文件生成到数据库所在的服务器上,然后通过数据库本身的备份恢复机制将数据导入到db中,下面是具体操作代码

通过BCP工具将SQL
SERVER中的数据导出为平面文件(Text或CSV格式),然后借助ORACLE的SQL
LOADER工具将数据导入数据库。


    环境:spring boot-maven-mybatis-sqlserver-tomcat

优点:我还真找不出其优点,仅仅作为一种方法归纳在此。

1. 使用事务来插入:

使用拼接sql的方法,在批量插入的时候,mysql支持这种语法

insert into table values (a,b,c),(a1,b1,c1),...

这种方法在拼接字符串之后,执行sql语句都会在一个事务当中执行
查询网上的说法,每个事务中执行500或者1000左右需要提交一次事务,这样性能不会有所下降,也防止因为事务中的sql语句过多有错误回滚,没错的也插入不了,代码参考如下

  public static void MySqlExcuteBatch(List<string> sqlList, string conStr)
    {
        using (MySqlConnection conn = new MySqlConnection(conStr))
        {
            using (MySqlCommand cmd = new MySqlCommand())
            {
                cmd.Connection = conn;

                cmd.CommandType = CommandType.Text;
                if (conn.State != ConnectionState.Open)
                    conn.Open();
                MySqlTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    for (int n = 0; n < sqlList.Count; n++)
                    {
                        string strsql = sqlList[n].ToString();
                        if (strsql.Trim().Length > 1)
                        {
                            cmd.CommandText = strsql;
                            cmd.ExecuteNonQuery();
                        }

                        if (n > 0 && (n % 800 == 0 || n == sqlList.Count - 1))
                        {
                            tx.Commit();
                            tx = conn.BeginTransaction();
                        }
                    }
                }
                catch (Exception e)
                {
                    tx.Rollback();
                    throw new Exception(e.Message);
                }
            }
        }
    }
一、在service实现中创建含有导入数据的文件

@Service
public class DemoServiceImpl extends ServiceImpl<DemoMapper, Demo> implements DemoService {

    @Resource
    private DemoMapper dmapper;

    @Override
    public void insertDemo(List<Demo> demoList) throws Exception {
        if(ToolUtil.isNotEmpty(demoList)) {
            try{
                //生成存储过程TXT start
                Demo demo = new Demo();
                Demo testDemo = null;
                String filename = "D:/"+demoList.get(0).getFileId()+"demoFileName.txt";
                File newFile = new File(filename);
                StringBuffer sb = new StringBuffer();
                for(int i=0;i<demoList.size();i++){
                    testDemo = demoList.get(i);
                    ToolUtil.copyProperties(testDemo, demo);
                    sb.append(StringUtil.null2String(demo.filed1())).append(",");
                    sb.append(StringUtil.null2String(demo.filed2())).append(",");
                    sb.append(StringUtil.null2String(demo.filed3())).append("*");
                }
                Writer writer = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(newFile), "UTF-8"));
                writer.write(sb.toString());
                writer.flush();
                writer.close();
                //生成存储过程TXT End

                //调用存储过程
                dmapper.insertTxt(filename,"demoFileName");

            } catch (Exception e) {
                throw e;
            } 
        }

    }

}

二、工具类

public class StringUtil {

    /**
     * null或""转换String ""
     * 
     * */
    public static String null2String(String str){
        if(str==null||"".equals(str)) {
            return "";
        }
        return str;
    }

}



三、dao.xml中加入调用读取文件的sql

<select id="insertTxt">
        exec dbo.txt_insert @filename =#{filename},@tablename = #{tablename}
    </select>

四、执行导入的slq语句

ALTER procedure [dbo].[txt_insert]
@filename nvarchar(100),
@tablename nvarchar(100)

AS  
exec('  
bulk insert ' + @tablename + ' from ''' + @filename + '''  
WITH  

      (  

        FIELDTERMINATOR = '','',--列分割符  

        ROWTERMINATOR = ''*'',--行分割符  

  FIRSTROW = 1  ,  --指定要加载的第一行的行号。默认值是指定数据文件中的第一行  
  CHECK_CONSTRAINTS  

      );')

缺点:需要安装ORACLE CLIENT客户端,而且ORACLE
CLIENT客户端工具安装后必须重启服务器使之生效。效率不是最快的。尤其当导入的数据量非常大时,不推荐使用这种方法。

2.数据通过生成文件导入数据库

mysql支持把csv文件倒入到数据库,生成csv文件的时候,不需要特别生成一行头文件,每行生成的数据直接用
‘,’
分隔就行,一行代表数据库的一条记录,所以生成csv文件时候,记得数据的顺序

 public static void MySqlExcuteBatch(string conStr, string filePath, string tableName)
    {
        using (MySqlConnection conn = new MySqlConnection(conStr))
        {
            conn.Open();
            MySqlBulkLoader bulk = new MySqlBulkLoader(conn)
            {
                FieldTerminator = ",",//这个地方字段间的间隔方式,为逗号
                FieldQuotationCharacter = '"',
                EscapeCharacter = '"',
                LineTerminator = "rn",//每行
                FileName = filePath,//文件地址
                NumberOfLinesToSkip = 0,
                TableName = tableName,
            };
            bulk.Load();
        }
    }

澳门葡萄京官方网站 ,———————————有好的建议可以直接在评论中提出,大家一起讨论—————–

我们偶尔会有将数据从SQL
SERVER导入到ORACLE当中的这种需求,那么这种跨数据库导数有那些方法呢?这些方法又有那些利弊呢?
下面比较肤浅的总结了一些可行的方法。

总结

当数据量不大的时候可以优先选择第一种方法,只不过拼接sql语句有点麻烦

当数据量大的时候优先选择第二种方法,速度比第一种方法快n倍

Tip:创建表的时候,索引要选择好,本人亲测990万条数据插入一张表,表有一个datetime类型的字段索引,第二种方法比第一种方法快4分钟左右。把索引换成string类型的字段,第二种方法耗时是原先的5倍左右。还有网上说的字段能用string类型就不要用其他类型,我测试一下,datetime类型换成string类型,反而插入速度很慢。

mysql的配置文件也可以优化一下,会大大提高插入速度。

————————————-大数据量导入方案—————————–

  
创建链接到ORACLE数据库的链接服务器非常简单,在此不做过多描述。一般使用这种方式做数据交互的情况较多。

 

 

缺点:1:效率不高问题,有一次导入70多万数据,耗时非常久。速度非常慢。当然我没有大量测试验证。不知道不同的数据库连接驱动Microsoft
OLE DB Provider for Oracle 、Oracle Provide for OLE DB、.Net Framework
Data Provider for Oracle之间的效率是否有较大区别。

 

优点:图形化操作、快捷方便。不需要写代码(或只需要写很少代码)。

澳门葡萄京官方网站 1

CREATE TABLE TEST

(

 

    ID   INT ,

 

    NAME VARCHAR(12)

 

)

 

INSERT INTO dbo.TEST

 

SELECT 1001, 'Kerry' UNION ALL

 

SELECT 1002, 'Jimmy' UNION ALL

 

SELECT 1003, 'Ken'   UNION ALL

 

SELECT 1004, 'Richard'

 

SELECT 'INSERT INTO TEST '

 

      +'SELECT ' + CAST(ID AS VARCHAR(12)) +' AS ID,'''

 

      + NAME + ''' FROM DUAL;'

 

FROM dbo.TEST

适用场景:适用于绝大多数数据量不大的场景。尤其适合一次性导数。

优点:链接服务器创建好后,可以非常方便的使用SQL导入数据到ORACLE。而且可以反复使用。

 

适用场景:适用于系统经常需要在SQL
SERVER与ORACLE之间数据交互的场景,这样就可以方便的利用它来导数,而不用为了导数在服务器上装ORACLE
CLIENT客户端工具。尤其适合经常需要数据交互的场景。

缺点: 

适用场景:大数据的导入导出。

缺点:操作麻烦,每次操作都要写脚本;数据量大时性能非常糟糕。

3:使用SQL SERVER的导入导出工具

 

 

SQL Server的导入导出工具(SQL Server Import and Export
Data),可以在很多不同数据库之间进行导数。

    1:需要写一些脚本。如果有非常多的表需要导数,工作量较大

    2:平面文件的格式会导致SQL
LOADER装载数据出现较多小问题。如果你精通SQL*LOADER的话,那么就可以避免踩到那些坑。

适用场景:仅仅使用小表、基础数据表。例如省份表之类。它能做到的,SQL
SERVER导入导出工具能比其跟方便、快捷的做到。

4:借助BCP和SQL*LOADER导入数据

2:创建链接服务器链接到ORACLE数据库

     2:出错时提示信息有时候不明确(出错信息不具体),用户体验非常糟糕。

优点:
效率高、速度快,尤其导数的数据量非常大的情况下。SQL*LOADER的直接路径装载效率非常高:(direct
path):采用这种模式,SQLLDR不使用SQL;而是直接格式化数据库块。

 

1:生成SQL脚本然后去ORACLE数据库执行。