SQLite vb.net 数据表中的一个命令中的多个插入

Question

我有这个代码，但它只插入了 7 条记录中的 54 条。我确信数据表中的记录很好。

Dim dt As DataTable
Dim sc As SQLiteCommand
Dim Script As String = Nothing
Dim Script2 As String = Nothing
Dim Script3 As String = Nothing
Script = "insert into paperdate(dtime,papernum,paperstat,user) select " & "'" & dt.Rows(0)(0) & "'" & " as " & "dtime" & "," & dt.Rows(0)(1) & " as " & "papernum" & "," & "'" & dt.Rows(0)(2) & "'" & " as " & "paperstat" & "," & "'" & dt.Rows(0)(3) & "'" & " as " & "user"
For i As Integer = 1 To dt.Rows.Count - 1
Script2 = " union select " & "'" & dt.Rows(i)(0) & "'" & "," & dt.Rows(i)(1) & "," & "'" & dt.Rows(i)(2) & "'" & "," & "'" & dt.Rows(i)(3) & "'"
Script3 = Script3 & Script2
Next
sc = New SQLiteCommand(Script & Script3, mycon)
sc.ExecuteNonQuery()
sc.Dispose()

我希望有人有答案，谢谢

Answer 1

可能是

您在第二个选择中有重复的记录。如果是这种情况，那么解决方案是使用 UNION ALL 而不是 UNION .

使用 UNION ALL 运算符创建的复合 SELECT 返回所有 从 SELECT 到联盟 ALL 运算符左侧的行，以及所有 右侧的 SELECT 中的行。联盟操作员工作 与 UNION ALL 相同，只是从中删除了重复的行 最终结果集。

参考： http://www.sqlite.org/lang_select.html

如果不是这样，那么您可能希望跟踪 SQL，并直接在 sqlite 客户端中运行SELECT部分。

更新

根据您的评论，以下是您对代码所做的更改：

Script2 = " union select " & "'" & dt.Rows(i)(0) & "'" & "," & dt.Rows(i)(1) & "," & "'" & dt.Rows(i)(2) & "'" & "," & "'" & dt.Rows(i)(3) & "'"

成为

Script2 = " union all select " & "'" & dt.Rows(i)(0) & "'" & "," & dt.Rows(i)(1) & "," & "'" & dt.Rows(i)(2) & "'" & "," & "'" & dt.Rows(i)(3) & "'"