(高分求救)如何循环插入10万多条纪录

[sunhp]

我有一个TXT文件，里面有10万多条纪录，我现在采用的方法是例遍每行往数据库里插，可是每次大概能插到2万到6万就出错了，记录太多该怎么插呢？各位大虾有没有什么好的建议。

'打开文件
stream = New StreamReader(file.OpenRead, System.Text.Encoding.Default)
LInputDataConn = New Data.OleDb.OleDbConnection(connString)
'打开连接
LInputDataConn.Open()

'例遍上传文件
Do
strFilePerLine = stream.ReadLine()
If strFilePerLine = "" Then Exit Do
'得到插入数据库得sql语句
strsql = GetInsertSql(strFilePerLine)
Try
LInputDataCmd = New OleDb.OleDbCommand(strsql, LInputDataConn)
LInputDataCmd.ExecuteNonQuery()
Catch ex As Exception
FaileCount += 1
End Try
Loop

[chinchy]

BULK INSERT PUBS.DBO.AUTHORS FROM 'E:\A.TXT'

权限
只有 sysadmin 和 bulkadmin 固定服务器角色成员才能执行 BULK INSERT

A.TXT
======================================================
A172-32-1176 White Johnson 408 496-7223 10932 Bigge Rd. Menlo Park CA 94025 1
A213-46-8915 Green Marjorie 415 986-7020 309 63rd St. #411 Oakland CA 94618 1
A238-95-7766 Carson Cheryl 415 548-7723 589 Darwin Ln. Berkeley CA 94705 1
A267-41-2394 O'Leary Michael 408 286-2428 22 Cleveland Av. #14 San Jose CA 95128 1
A274-80-9391 Straight Dean 415 834-2919 5420 College Av. Oakland CA 94609 1





==========================================================

[sunhp]

思归大虾，帮人帮到底，用你的方法来做的代码有么？我写得不行啊
还有我的是oracle或是sybase数据库,不能直接bulk
我的代码
Dim myConn As New OleDbConnection(connString)
Dim myDataAdapter As New OleDbDataAdapter()
myDataAdapter.SelectCommand = New OleDbCommand("select * from " & PageTableName & " where 1<>1", myConn)
Dim myCommandBuilder As OleDbCommandBuilder = New OleDbCommandBuilder(myDataAdapter)
myConn.Open()
Dim LDataSet As DataSet = New DataSet()
myDataAdapter.Fill(LDataSet, "UploadTable")
'例遍上传文件，操作datatable
Do
strFilePerLine = stream.ReadLine()
If strFilePerLine = "" Then Exit Do
LDataSet.Tables("UploadTable").Rows.Add(NewRow)
'这块加了很多行，略
Loop
myDataAdapter.Update(LDataSet, "UploadTable")
myConn.Close()
报错，错误信息
对于不返回任何基表信息的 SelectCommand 不支持动态 SQL 生成
该怎么做？

[saucer]

try something like


Dim myConn As New OleDbConnection(connString)
Dim myDataAdapter As New OleDbDataAdapter()
myDataAdapter.SelectCommand = New OleDbCommand("select * from " & PageTableName, myConn)
Dim myCommandBuilder As OleDbCommandBuilder = New OleDbCommandBuilder(myDataAdapter)
myConn.Open()
Dim LDataSet As DataSet = New DataSet()
myDataAdapter.FillSchema(LDataSet, SchemaType.Source)
LDataSet.Tables(0).TableName = "UploadTable"

Do
strFilePerLine = stream.ReadLine()
If strFilePerLine = "" Then Exit Do
LDataSet.Tables("UploadTable").Rows.Add(NewRow)
'这块加了很多行，略
Loop
myDataAdapter.Update(LDataSet, "UploadTable")
myConn.Close()

[cchinasp]

你應該只是做一次性的數據導入吧.可以用sql的導入功能啊.

[cchinasp]

如果是經常性的,可能是你的程序架構有問題,你可以考慮改一下程序的架構.

[zwztu]

纪录太多了。

[Englishgenius]

分成几次执行
select top n from 表。。

[sunhp]

有没有用多线程插入的可能呢？

[childeliu]

分批

[saucer]

don't call tDataCmd.ExecuteNonQuery() every time you add a new record

insert your data in a DataTable, then use a DataAdapter to update it to the database