插入多行，根据另一个表列计数

问题描述

我有 3 张桌子.Item、StockDetail、Branch

I have 3 Tables. Item, StockDetail, Branch

我想一次插入其中的 2 个.Item 和 StockDetail 表.

I want to INSERT 2 of them at once. The Item and StockDetail table.

Item 有 3 列 = ItemID、Title、Price.

Item has 3 columns = ItemID, Title, Price.

StockDetail 有 3 列 = ItemID、BranchID、Stock.

StockDetail has 3 columns = ItemID, BranchID, Stock.

Branch 有 1 列 = BranchID.

在下面的代码中，INSERT into Item 工作正常，但不适用于 StockDetail 表，它不会插入任何内容！

In this code below, INSERT into Item works fine, but not for StockDetail table, it doesn't INSERT anything!

现在对于 StockDetail 如果它有效，我想用下面的条件插入它:

Now for the StockDetail if it works, I want to insert it with the condition below:

如果你添加一个项目，那么它会添加这个项目以及所有现有的 BranchID.

If you add an item, then it'll add this item with all existed BranchID.

也就是说，每个分店都会有这个项目.

That mean, every Branches will have this item.

例如:

您添加一个项目，同时

Branch 有 3 行 BranchID = BR000, BR001, BR002.

Branch has 3 rows of BranchID = BR000, BR001, BR002.

它将同时插入 3 行的 StockDetail(单个查询)

It will insert to StockDetail with 3 rows as well, at once (single Query)

StockDetail的完整结果(单查询):

Complete result of StockDetail (single Query):

  ItemID | BranchID  | Stock
______________________________
  IM000  |   BR000   |   0
  IM000  |   BR001   |   0
  IM000  |   BR002   |   0

<小时>

代码:

'Add function'
'Insert to StockDetail'
Dim theCommand As New SqlCommand
Dim theDataAdapter As New SqlDataAdapter
Dim theDataTable As New DataTable
theCommand.Connection = theConnection
theCommand.CommandText = "INSERT INTO StockDetail VALUES(
                         '" & Me.TextBox_ItemID.Text & "',
                         SELECT COUNT(BranchID) FROM Branch,
                         '0'
                         )"
theDataAdapter.SelectCommand = theCommand
'Insert to Item'
theCommand.Connection = theConnection
theCommand.CommandText = "INSERT INTO Item VALUES('" & Me.TextBox_ItemID.Text & "', '" & Me.TextBox_Title.Text & "', '" & Me.TextBox_Price.Text & "')"
theDataAdapter.SelectCommand = theCommand
theDataAdapter.Fill(theDataTable)
DataGridView_Item.DataSource = theDataTable
theCommand.Dispose()
theDataAdapter.Dispose()

更新:

下面的代码将告诉您工作中的多个 INSERT，但不包括 BranchID INSERT.

The code below will tell you the working multiple INSERT, but not with the BranchID INSERT.

'Insert to StockDetail'
theConnection.Open()
Dim theCommand As New SqlCommand
Dim theDataAdapter As New SqlDataAdapter
theCommand.Connection = theConnection
theCommand.Parameters.Add("@ItemID", SqlDbType.VarChar).Value = Me.TextBox_ItemID.Text
theCommand.CommandText = "INSERT INTO StockDetail(ItemID, BranchID, Stock) SELECT @ItemID, COUNT(Branch.BranchID), '0' FROM Branch GROUP BY Branch.BranchID"
theDataAdapter.SelectCommand = theCommand
Using theDataAdapter
    theCommand.ExecuteNonQuery()
    theCommand.Parameters.Clear()
    theCommand.Dispose()
    theConnection.Close()
    SqlConnection.ClearPool(theConnection)
End Using

我现在想要什么?

好吧，而不是插入 1, 1, ...

Well instead of inserting 1, 1, . . .

我想用 BR000, BR001 插入它...(基于所有存在的BranchID)

I'd like to INSERT it with BR000, BR001 . . . (Based on all existed BranchID)

推荐答案

以下是在第一个插入语句中使用参数的方法.我认为你在这里仍然有一些非常严重的逻辑问题.这将在 StockDetail 中插入 1 行，这些值根本没有任何意义.您将从 Branch 表中插入行数作为 BranchID，这可能不是您真正想要的.我怀疑你想要的是这个表中每个分支的一行?

Here is how you use a parameter in your first insert statement. I think you still have some very serious logic issues here though. This is going to insert 1 row into StockDetail and the values don't make any sense at all. You would be inserting the count of rows from the Branch table as the BranchID which is probably not what you really want. I suspect what you want is a row in this table for each Branch?

theCommand.CommandText = "INSERT INTO StockDetail(ItemID, BranchID, Price) VALUES(
                         @ItemID,
                         (SELECT COUNT(BranchID) FROM Branch),
                         0
                         )"
theCommand.Parameters.Add("@ItemID", SqlDbType.Varchar).Value = Me.TextBox_ItemID.Text;

我怀疑你真正想要的是更像这样的东西.

I suspect what you really want is something more like this.

theCommand.CommandText = "INSERT INTO StockDetail(ItemID, BranchID, Price) 
                        select @ItemID
                            , BranchID
                            , 0
                        from Branch";
theCommand.Parameter.Add("@ItemID", SqlDbType.Varchar).Value = Me.TextBox_ItemID.Text;

这篇关于插入多行，根据另一个表列计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！