运行不带参数的INSERT INTO语句

本文关键字：INSERT INTO 语句 参数 运行 | 更新日期: 2023-09-27 17:59:43

我目前有一个功能性的INSERT INTO语句，只需单击一个按钮，它就可以很好地工作。由于这个项目的额外需求，我有第二个INSERT INTO查询可以工作，但现在我需要两者协同工作。我如何接受两个INSERT查询，并使它们成为一个INSERT INTO语句，因为另一种方式是复制行（这是由于添加了两个新列而导致的）。我在下面发布了以下查询：

从gridview到Informix数据库的当前INSERT INTO：

private void btnInsert_Click(object sender, EventArgs e)
{
OdbcDataAdapter da2 = new OdbcDataAdapter();
for (int i = 0; i < dataGridView2.Rows.Count; i++)
{
String insertData = "INSERT INTO brev_efile_user_doc_stats (trans_nbr, sequence_id, received_on, description, register_action) VALUES (?,?,?,?,?)";
OdbcCommand cmd = new OdbcCommand(insertData, connection);
cmd.Parameters.AddWithValue("@trans_nbr", dataGridView2.Rows[i].Cells[0].Value);
cmd.Parameters.AddWithValue("@sequence_id", dataGridView2.Rows[i].Cells[1].Value);
cmd.Parameters.AddWithValue("@received_on", dataGridView2.Rows[i].Cells[2].Value);
cmd.Parameters.AddWithValue("@description", dataGridView2.Rows[i].Cells[3].Value);
cmd.Parameters.AddWithValue("@register_action", dataGridView2.Rows[i].Cells[4].Value);
  da2.InsertCommand = cmd;
  try
    {
    connection.Open();
    cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                connection.Close();
            }
        }
    }

第二个INSERT INTO用于显示来自XML的校正序列（并不总是正确的）：

INSERT INTO brev_efile_user_doc_stats
SELECT trans_nbr,received_on,description,register_action,sequence_id, reviewed_event_code,     
ROW_NUMBER() OVER(PARTITION BY trans_nbr 
ORDER BY trans_nbr,sequence_id) AS corrected_seq_id
FROM brev_efile_user_doc_stats
WHERE trans_nbr IS NOT NULL 
ORDER BY trans_nbr,sequence_id

运行不带参数的INSERT INTO语句

我相信您应该能够通过使用UNION ALL在一个语句中完成插入。只需确保第一个select语句的列数（在本例中为参数）与第二个select语句相同。我为@reviewed_event_code和@corrected_seq_id添加了额外的参数。您需要像在ODBCommand对象上设置其他参数值一样设置参数值。我希望这能有所帮助！

INSERT INTO brev_efile_user_doc_stats
SELECT @trans_nbr, @received_on, @description, @register_action, @sequence_id, @reviewed_event_code, @corrected_seq_id
UNION ALL
SELECT trans_nbr,received_on,description,register_action,sequence_id, reviewed_event_code,
ROW_NUMBER() OVER(PARTITION BY trans_nbr ORDER BY trans_nbr, received_on) AS corrected_seq_id
FROM brev_efile_user_doc_stats
WHERE trans_nbr IS NOT NULL 
ORDER BY trans_nbr,corrected_seq_id
相关文章：