我將Text file中的資料讀到一個local的Recordset中, 然後再抄到SQL Server 中一個結構一模一樣的Table中, 以下是 Pseudo code:
Define an ADO Recordset (use Recordset.Append...)
Open Recordset
Read data from text file into Recordset
Open a SQL server Table as a ADO Recordset (rsTarget, adLockBatchOptimistic)
Delete all data in rsTarget (using SQL statement, this step got done really fast.)
Disconnect rsTarget
While Not rsSource.Eof
rsTarget.Addnew
copy a record from rsSource to rsTarget
rsSource.MoveNext
Wend
Re-connect rsTarget
rsTarget.UpdateBatch
基於某些原因, 我不能直接把資料寫進連接到SQL server的Recordset, 而必須透過一個中介的Recordset. 問題是當資料量大時, 速度實在令人不滿意, 請問各位有無更快的方法? 同樣的資料量, 從Excel中下MS Query, 幾乎是瞬間就從SQL Server download下來了...