SQL Server TSQL MERGE Multiple Tables with Insert, Update, Delete

sf6xfgos 于 2023-10-15 发布在其他

关注(0)|答案(1)|浏览(113)

I'm currently working on a stored procedure in T-SQL on SQL Server 2012. The task is really tricky: I need to merge (insert,update,delete) entries in multiple tables.

To facilitate, I try to explain my problem with 2 tables. I use Dapper in C# in an ASP.NET 4.5 MVC application and can control my Front-End. I'm passing into the stored procedure 2 Temporary Tables:

Temporary Table Target:

ID | TargetId | TargetGroupId | IsPublic | IsPrivate | 
---+----------+---------------+----------+-----------+
0  |    42    |   1           |   0      |  1        |
1  |    0     |   1           |   1      |  0        |
2  |    0     |   1           |   0      |  1        |

Temporary Table Target Countries

ID |CountryId | TargetId | 
---+----------+----------+
0  |    CA    |   42     |
1  |    FR    |   0      |
2  |    AU    |   0      |

The TargetId 0 corresponds to a new entry, thus if the TargetId = 0 I want to Insert or Delete an old record not from the list, if the TargetId > 0 I want to Update the record.

The final Result could look like this:

Table Target:

| TargetId | TargetGroupId | IsPublic | IsPrivate | 
+----------+---------------+----------+-----------+
|    42    |   1           |   0      |  1        |
|    93    |   1           |   1      |  0        |
|    94    |   1           |   0      |  1        |

Table Country:

CId  |CountryId | TargetId | 
-----+----------+----------+
34   |    CA    |   42     |
35   |    FR    |   93     |
36   |    AU    |   94     |

So the new TargetId should be stored in Country as well. My current TSQL script looks like this:

DECLARE @TargetGroupId = 1;

MERGE [MySchema].[Target] AS [A]
USING @Targets AS [B]
ON ([A].TargetId = [B].TargetId)
WHEN NOT MATCHED BY TARGET THEN
    INSERT (TargetGroupId, IsPrivate, IsPublic)
    VALUES (@TargetGroupId, [B].IsPrivate, [B].IsPublic)

    -- Desired behaviour: doesn't work unfortunately...
    -- OUTPUT inserted.TargetId, [B].ID
    -- INTO #tmp;
WHEN MATCHED THEN
    UPDATE
    SET [A].IsPrivate = [B].IsPrivate, [A].IsPublic = [B].IsPublic
WHEN NOT MATCHED BY SOURCE AND [A].TargetGroupId = @TargetGroupId
    THEN DELETE;

This SQL script works fine for my table Target, but how can I update my second table Countries?

Do you know how solve this issue, thus to Insert, Update, Delete in a Merge Statement including several tables?

Thank you very much!!

sql-server

来源：https://stackoverflow.com/questions/44282122/tsql-merge-multiple-tables-with-insert-update-delete

1条答案

按热度按时间

qyuhtwio1#

SqlConnection conn = new SqlConnection("Data source=.;initial catalog=db_futsal;integrated security=true");
    private void Peserta_Load(object sender, EventArgs e)
    {
        bind_data();
    }
    private void bind_data()
    {
        SqlCommand cmd1 = new SqlCommand("Select no_peserta,nama As nama,asal As asal,tanggal_lahir from peserta", conn);
        SqlDataAdapter da = new SqlDataAdapter();
        da.SelectCommand = cmd1;
        DataTable dt = new DataTable();
        dt.Clear();
        da.Fill(dt);
        dataGridView1.DataSource = dt;
    }

    private void button1_Click(object sender, EventArgs e)
    {
        SqlCommand cmd2 = new SqlCommand("Insert into peserta(no_peserta,nama,asal,tanggal_lahir)Values(@no_peserta,@nama,@asal,@tanggal_lahir)",conn);
        cmd2.Parameters.AddWithValue("no_peserta", textBox1.Text);
        cmd2.Parameters.AddWithValue("nama", textBox2.Text);
        cmd2.Parameters.AddWithValue("asal", textBox3.Text);
        cmd2.Parameters.AddWithValue("tanggal_lahir", dateTimePicker1.Value);
        conn.Open();
        cmd2.ExecuteNonQuery();
        conn.Close();
        bind_data();
    }

    private void button2_Click(object sender, EventArgs e)
    {
        textBox1.Text = "";
        textBox2.Text = "";
        textBox3.Text = "";
        dateTimePicker1.Text = "";
    }

    private void dataGridView1_CellClick(object sender, DataGridViewCellEventArgs e)
    {
        int index;
        index = e.RowIndex;
        DataGridViewRow selectedrow = dataGridView1.Rows[index];
        textBox1.Text = selectedrow.Cells[0].Value.ToString();
        textBox2.Text = selectedrow.Cells[1].Value.ToString();
        textBox3.Text = selectedrow.Cells[2].Value.ToString();
        dateTimePicker1.Text = selectedrow.Cells[3].Value.ToString();

    }

    private void button3_Click(object sender, EventArgs e)
    {
        SqlCommand cmd3 = new SqlCommand("Update peserta Set nama=@nama,asal=@asal,tanggal_lahir=@tanggal_lahir where no_peserta=@no_peserta", conn);
        cmd3.Parameters.AddWithValue("no_peserta", textBox1.Text);
        cmd3.Parameters.AddWithValue("nama", textBox2.Text);
        cmd3.Parameters.AddWithValue("asal", textBox3.Text);
        cmd3.Parameters.AddWithValue("tanggal_lahir", dateTimePicker1.Value);
        conn.Open();
        cmd3.ExecuteNonQuery();
        conn.Close();
        bind_data();
    }

    private void button4_Click(object sender, EventArgs e)
    {
        SqlCommand cmd4 = new SqlCommand("Delete from peserta where no_peserta=@no_peserta", conn);
        cmd4.Parameters.AddWithValue("no_peserta", textBox1.Text);
        conn.Open();
        cmd4.ExecuteNonQuery();
        conn.Close();
        bind_data();
    }

赞(0）回复(0）举报 2023-10-15

我来回答

SQL Server TSQL MERGE Multiple Tables with Insert, Update, Delete

1条答案

相关问题

热门标签

最新问答