在线等。。。从Excel中读取数据到DataTable中循环插入到Oracle中耗时长的问题怎么解决？？？？？？？？？

smputao 2012-05-09 12:41:39

如题,现有一个excel数据文档,想把excel里面的数据都导入到oracle数据库中

1.先从Excel中读取数据到DataTable中。
2.循环DataTable，插入到Oracle中。

但是有的excel最多的有6W多行数据循环插入太慢有什么好办法呢？

...全文

236 6 打赏收藏转发到动态举报

写回复

用AI写文章

6 条回复

切换为时间正序

请发表友善的回复…

发表回复

xudlQQ123 2012-05-15

打赏
举报

我做过和你类似的一个上传项目,同时导入8张表.导入到Oracle数据中.其中的复杂度要比你这个要复杂的多.数据量也很大.但是用的时间却很少.在net写一个公共事物方法.逐条生成一条SQL语句,放到ArraList中,进行批量插入.如果你要想要的话,我这里有源码,我可以发给你.挺复杂的.
QQ:740303282
你可以加我,我明天拿U盘拷给你.

anzhiqiang_touzi 2012-05-15

打赏
举报



create table #table (ID char(1),Name char(100))

 



INSERT INTO #table

select 1 as ID,'Nam1' as Name where not exists(select NULL from #table where ID=1)



INSERT INTO #table

select 2 as ID,'Nam2' as Name where not exists(select NULL from #table where ID=2)



INSERT INTO #table

select 3 as ID,'Nam3' as Name where not exists(select NULL from #table where ID=3)



INSERT INTO #table

select 4 as ID,'Nam2' as Name where not exists(select NULL from #table where ID=4)



INSERT INTO #table

select 5 as ID,'Nam2' as Name where not exists(select NULL from #table where ID=5)





INSERT INTO #table

select 5 as ID,'Nam2' as Name where not exists(select NULL from #table where ID=5)

INSERT INTO #table

select 5 as ID,'Nam2' as Name where not exists(select NULL from #table where ID=5)

INSERT INTO #table

select 5 as ID,'Nam2' as Name where not exists(select NULL from #table where ID=5)

INSERT INTO #table

select 5 as ID,'Nam2' as Name where not exists(select NULL from #table where ID=5)

INSERT INTO #table

select 5 as ID,'Nam2' as Name where not exists(select NULL from #table where ID=5)





select * from #table

drop table #table

我用sqlerver导入是这么做的
你改成Oracle的就行了

smputao 2012-05-15

打赏
举报

不加验证也很慢因为是给客户用所以只要代码导入的方法
现在用的是以下这种方法导入的：
string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'";
OleDbConnection ExcelConn = new OleDbConnection(strCon);
try
{
string strCom = string.Format(sql);
ExcelConn.Open();
OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, ExcelConn);
DataSet ds = new DataSet();
myCommand.Fill(ds, "[" + tableName + "$]");
ExcelConn.Close();
ExcelConn.Dispose();
return ds;
}
catch
{
ExcelConn.Close();
ExcelConn.Dispose();
return null;
}
这种效率低