大批量插入sqlserver数据

╰+攻爆jí腚メ 2022-05-18 04:50 320阅读 0赞

大批量插入sqlserver数据

工作需要测试数据，每张表最大一亿条数据，30至60个字段，原本写好了sql脚本直接运行，但是使用insert循环插入实在太慢了，平均只有每秒3000条，照这个速度短期无法完成。

网上百度一番，各种方法都有，选择一种比较简单的，使用jdbc批量插入sqlserver，废话不多说，上代码：

import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.PreparedStatement;
    import java.sql.SQLException;
    import java.text.SimpleDateFormat;
    import java.util.Date;
    import java.util.UUID;
    
    public class batch {
    	public static void main(String[] args) throws ClassNotFoundException, SQLException {
    		batchInsert();
    	}
    
    	public static void batchInsert() throws ClassNotFoundException, SQLException {
    		// 起始时间
    		long start = System.currentTimeMillis();
    		// 连接
    		Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
    		Connection connection = DriverManager.getConnection("jdbc:sqlserver://192.168.1.xx:1433;DatabaseName=xxx",
    				"xxx", "xxx");
    
    		connection.setAutoCommit(false);
    		// 执行插入
    		PreparedStatement cmd = connection.prepareStatement(
    				"insert into [xxx].[dbo].[BATCH] values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
    
    		int n = 0;
    
    		SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:MM:ss.SSS");
    
    		// 注意，此处一次性插入一亿条会溢出，导致程序崩溃，因此最好每3000万到5000万条数据一次，3000万条约需要20分钟
    		// 把运行文件与数据库放在同一环境下速度更快
    		// int num=1;
    		for (int num = 0; num < 100000001; num++) {// 10000万条数据
    			cmd.setInt(1, num);
    			cmd.setInt(2, 921681220);
    			cmd.setString(3, "rico" + num);
    
    			cmd.setString(4, null);
    			cmd.setString(5, null);
    
    			cmd.setString(6, df.format(new Date()));
    			cmd.setString(7, df.format(new Date()));
    			cmd.setString(8, "America/Los_Angeles");
    
    			cmd.setString(9, null);
    			cmd.setString(10, null);
    
    			cmd.setInt(11, 0);
    			cmd.setInt(12, num);
    			cmd.setInt(13, 0);
    
    			cmd.setString(14, null);
    			cmd.setString(15, null);
    
    			if ((num - 1) % 500 == 0) {
    				n++;
    			}
    			cmd.setInt(16, n);
    
    			cmd.setString(17, df.format(new Date()));
    			cmd.setString(18, df.format(new Date()));
    			cmd.setString(19, "America/Los_Angeles");
    
    			cmd.setInt(20, n);
    
    			cmd.setString(21, df.format(new Date()));
    			cmd.setString(22, df.format(new Date()));
    			cmd.setString(23, "America/Los_Angeles");
    
    			cmd.setInt(24, n);
    
    			cmd.setInt(25, n);
    
    			cmd.setString(26, "{" + UUID.randomUUID().toString().toUpperCase() + "}");
    
    			cmd.setString(27, null);
    			cmd.setString(28, null);
    			cmd.setString(29, null);
    			cmd.setString(30, null);
    
    			cmd.setInt(31, 105);
    
    			cmd.setString(32, null);
    			cmd.setString(33, null);
    			cmd.setString(34, null);
    
    			cmd.addBatch();
    			if (num % 100000 == 0) {
    				cmd.executeBatch();
    				System.out.println(num);
    			}
    		}
    		cmd.executeBatch();
    		connection.commit();
    
    		cmd.close();
    		connection.close();
    
    		long end = System.currentTimeMillis();
    		System.out.println("批量插入需要时间:" + (end - start));
    	}
    }

注意事项：

1.使用此种方法一次插入循环3000万至5000万次，再大会导致内存溢出，每10万条commit一次。

2.把此代码运行在数据库同一环境下速度更快

3000万条字段30个的数据大概需要时间20分钟，分三批次插入，这样一亿条数据只需要一个小时左右。最终我的测试数据共5个数据库每个库7张表，总量达400g的数据终于插完了。

github源码：[https://github.com/ricozhou/batchinsertsqlserver][https_github.com_ricozhou_batchinsertsqlserver]

[https_github.com_ricozhou_batchinsertsqlserver]: https://github.com/ricozhou/batchinsertsqlserver