暱稱: joey0415
中學生
- 帖子
- 361
- 主題
- 57
- 精華
- 0
- 積分
- 426
- 點名
- 0
- 作業系統
- win7
- 軟體版本
- 2003,2010
- 閱讀權限
- 20
- 性別
- 男
- 註冊時間
- 2010-5-13
- 最後登錄
- 2022-12-8
|
4#
發表於 2013-8-7 16:08
| 只看該作者
找到解決的方法一，非正式…
sql語句例如
INSERT INTO "表格名" ("欄位1", "欄位2", ...)VALUES ("值1", "值2", ...);
sqlite它允許
INSERT INTO "表格名" ("欄位1", "欄位2", ...)VALUES ("值1", "值2", ...),("值3", "值4", ...),("值5", "值6", ...);
的方法，一次最多輸入500組數值，超過後一樣會報錯
如果你有1534組要insert進資料庫中，可以分成四組 34 500 500 500 的方法分批加進資料庫中
如果是一組組輸入，上例大概要30秒以上
如果是分四組輸入，估不用2秒
以下是個人程式碼：僅供參考- Sub sqlite批次insert多筆成功()
- '最多一次500條，要分段放進去
- Dim x, rcnt, i
- Dim sql_command As String
- Dim connection_string As String
-
- Worksheets(1).Activate
- rcnt = Range("a1").CurrentRegion.Rows.Count
- 倍數 = (rcnt \ 500)
- zx = rcnt Mod 500 '找500餘數
-
- j = 0
- If rcnt < 500 Then '如果小於500時只計算下面資料
-
- aa = "insert into dbs(yyyymmdd, stockname, tradercode, price, buyamount, sellamount) values"
- Set x = CreateObject("adodb.recordset")
- cc = ""
- cc = aa
- i = 1
- For i = 1 To rcnt
- bb = "('" & Cells(i, 1) & "','" & Cells(i, 2) & "','" & Cells(i, 3) & "','" & Cells(i, 4) & "','" & Cells(i, 5) & "','" & Cells(i, 6) & "')"
- '串接要insert的數值對
- cc = cc & bb & ","
- Next
-
- cc = Left(cc, Len(cc) - 1) '取字串並刪除最後一個","
- sql_command = cc
- connection_string = ("Driver={SQLite3 ODBC Driver};database=D:\股票資料下載\dbs.sqlite")
- x.Open sql_command, connection_string
- Set x = Nothing
- Else '如果大於500時，要算下面兩種資料
-
- aa = "insert into dbs(yyyymmdd, stockname, tradercode, price, buyamount, sellamount) values"
- Set x = CreateObject("adodb.recordset")
- cc = ""
- cc = aa
- i = 1
- For i = 1 To zx
-
- bb = "('" & Cells(i, 1) & "','" & Cells(i, 2) & "','" & Cells(i, 3) & "','" & Cells(i, 4) & "','" & Cells(i, 5) & "','" & Cells(i, 6) & "')"
- cc = cc & bb & ","
- Next
- cc = Left(cc, Len(cc) - 1)
- sql_command = cc
- connection_string = ("Driver={SQLite3 ODBC Driver};database=D:\股票資料下載\dbs.sqlite")
- x.Open sql_command, connection_string
- Set x = Nothing
- For j = 0 To 倍數 - 1
-
- aa = "insert into dbs(yyyymmdd, stockname, tradercode, price, buyamount, sellamount) values"
- Set x = CreateObject("adodb.recordset")
- cc = ""
- cc = aa
- i = 1
-
- For i = j * 500 + zx + 1 To (j + 1) * 500 + zx
-
- bb = "('" & Cells(i + j * 500, 1) & "','" & Cells(i + j * 500, 2) & "','" & Cells(i + j * 500, 3) & "','" & Cells(i + j * 500, 4) & "','" & Cells(i + j * 500, 5) & "','" & Cells(i + j * 500, 6) & "')"
- cc = cc & bb & ","
-
- Next
-
- cc = Left(cc, Len(cc) - 1)
- sql_command = cc
- connection_string = ("Driver={SQLite3 ODBC Driver};database=D:\股票資料下載\dbs.sqlite")
- x.Open sql_command, connection_string
- Set x = Nothing
- Next
-
- End If
- End Sub
|
|
