mysql - 使用for循环插入多个数据mysql

在这种情况下，我有一个选择查询，然后所有提取的数据都必须插入到另一个表中。

如果我要删除for循环。
例：
提取的数据为id1，id2，id3
我数据库中插入的数据是id1，id1，id1，而不是id1，id2，id3

sql = "SELECT * FROM dummy_violate WHERE res_month <> @month Or res_year <> @year"
    cmd = New MySqlCommand(sql, con)
    cmd.Parameters.AddWithValue("@month", DateTime.Now.ToString("MMMM"))
    cmd.Parameters.AddWithValue("@year", DateTime.Now.ToString("yyyy"))
    dr = cmd.ExecuteReader
    While dr.Read
        id += dr(0)
        count += 1
    End While
    dr.Close()
    If count > 0 Then

        For i As Integer = 1 To count
            sql2 = "INSERT INTO dummy_violate(res_id, res_month, res_year, is_paid)VALUES(@id,@month,@year,@paid)"
            cmd = New MySqlCommand(sql2, con)
            cmd.Parameters.AddWithValue("@id", id)
            cmd.Parameters.AddWithValue("@month", DateTime.Now.ToString("MMMM"))
            cmd.Parameters.AddWithValue("@year", DateTime.Now.ToString("yyyy"))
            cmd.Parameters.AddWithValue("@paid", 0)
            cmd.ExecuteNonQuery()
        Next
    ElseIf count = 0 Then

        MsgBox("Wrong Query")

    End If

最佳答案

我不太了解您要做什么以及为什么要这样做，似乎您只是在将表中已经存在的记录与具有相同数据但is_paid标志设置为零的新记录进行复制。如果是这样，请尝试以这种方式更改代码：

sql = "SELECT res_id FROM dummy_violate WHERE res_month <> @month Or res_year <> @year"
cmd = New MySqlCommand(sql, con)
cmd.Parameters.AddWithValue("@month", DateTime.Now.ToString("MMMM"))
cmd.Parameters.AddWithValue("@year", DateTime.Now.ToString("yyyy"))
Dim da = new MySqlDataAdapter(cmd)
Dim dt = new DataTable()
da.Fill(dt)

' if we have records to duplicate we have Rows.Count > 0
If dt.Rows.Count > 0 Then
   sql2 = "INSERT INTO dummy_violate(res_id, res_month, res_year, is_paid) " & _
          "VALUES(@id,@month,@year,@paid)"
    cmd = New MySqlCommand(sql2, con)

    ' Add all the parameters before entering the loop. Just @id changes for every loop,
    ' so we set it with a dummy value outside the loop and we change it when looping over
    ' the result table.....
    cmd.Parameters.AddWithValue("@id", 0)
    cmd.Parameters.AddWithValue("@month", DateTime.Now.ToString("MMMM"))
    cmd.Parameters.AddWithValue("@year", DateTime.Now.ToString("yyyy"))
    cmd.Parameters.AddWithValue("@paid", 0)

    ' execute the insert for all the rows count
    ' rows array starts at zero so loop to count -1
    For i As Integer = 0 To dt.Rows.Count - 1
        cmd.Parameters("@id").Value = dt.Rows(i)("res_id")
        cmd.ExecuteNonQuery()
    Next
ElseIf count = 0 Then
    MsgBox("Wrong Query")
End If

请记住，如果res_id是主键，则不能两次插入具有相同res_id的记录。另外，如果res_id是一个auto_number列（也称为IDENTITY），则您不能明确设置其值