Question

怎么批量插入mysql数据库(1000条)

怎么批量插入mysql数据库(2000条),需不需要写另外的程序?或者是有什么直接的sql语句?更有效的方法...

Answer 1

把后缀为.sql的命令文件导入数据库

source d:/1.sql;

注意：要先选择一个数据库，要不不能导入的.

Answer 2

楼至可以使用jdbc预编译+批量更新，这样的执行效率最高。
具体是这样使用的：
循环2000条数据例如数据被装到了一个名叫dataList的List<Map<String,String>>中，那么批量更新就是这样执行的
Map map = null;
String data1= "";
String data2 = "";
String sql = insert into biao values(?,?);
PreparedStatement preStmt = conn.prepareStatement(sql);
for(i = 0;i<dataList.size();i++){
map = dataList.get(i);
data1=map.get("data1");
data2 = map.get("data2");
preStmt.setString(1,data1);
preStmt.setString(2, data2);
preStmt.addBatch();
}
preStmt.executeBatch();
preStmt.clearBatch();

Answer 3

declare @index int
set @index = 1
while @index<=2000
begin
insert into 某表名 values (值)
set @index = @index +1
end
go

Answer 4

DROP PROCEDURE TestWhile;
CREATE PROCEDURE TestWhile()
BEGIN
DECLARE icount INT;
set icount = 30000;
while icount>0 DO
INSERT into baseinfo_product
(id,mainBarCode,name,code,pinyin,simpleName,status,origin,grade,expirationDate,season,mainUnitId,maxPurchasePrice,minSalePrice,isAssembling,isPackage,
isWeighing,isDiscount,description,specDescription,remarks,productGroupId,packingUnitId,createEnterpriseId,createEmployeeId,accountingMethod,
outputTaxRate,inputTaxRate,lastUpdateDate
)
values
(UUID(),'789','测试1','测试1','测试1','测试1','0','测试1','测试1','0','测试1','ed673ac5-cfe5-11e1-a2cf-4437e623a9bb',
'0.00','0.00','0','0','0','0','测试1','测试1','测试1','fe86b18a-cfe5-11e1-a2cf-4437e623a9bb','ed66e1cb-cfe5-11e1-a2cf-4437e623a9bb','a24fc6a4-6bbf-11e0-8299-4437e6285473',
'7e1e9faf-6bc0-11e0-8299-4437e6285473','0','0.00','0.00','2012-07-21 14:50:28'
);
set icount = icount-1;
END WHILE;

END;

Answer 5

insert into select (字段）from 表 有多少条记录就能插入多少