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

Question

这就是场景，我有一个选择查询，然后所有获取的数据必须插入到另一个表中。这就是我想到的，我不知道 for 循环是否做了任何事情。

如果我删除 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

Answer 1

我不太明白您要做什么以及为什么，您似乎只是将表中已有的记录复制为具有相同数据但 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)，那么您无法显式设置其值