SQL Server - 如何在子存储或表变量中的不同表中将选择结果插入到存储过程中的新表变量中？

Well the title is pretty complicated. What I basically want to do:

好吧标题很复杂。我基本上想做的事情:

I want my Stored Procedure to show the user something like a "Result Table" from what the procedure did. But I have extreme problems with inserting subquery values or table variable data with the "INSERT INTO" statement.

我希望我的存储过程向用户显示程序所做的“结果表”。但是我使用“INSERT INTO”语句插入子查询值或表变量数据时遇到了极大的问题。

The data which needs to be inserted in my result table (delcared as @resulttable) comes from 2 different tables. and from a table variable which I declared before (@allupdateditemIDs).

需要插入我的结果表(delcared为@resulttable)的数据来自2个不同的表。从我之前声明的表变量(@allupdateditemIDs)。

I already tried those scripts to make that possible:

我已经尝试过这些脚本来实现这一点:

    INSERT INTO @resulttable (UpdatedItemID, UpdatedItemName, NewProbability)
SELECT * FROM @allupdateditemIDs ORDER BY ItemNum ASC,
SELECT ItemName FROM ti_Item WHERE ItemNum IN (SELECT * FROM @allupdateditemIDs) ORDER BY ItemNum ASC,
SELECT MixingProbability FROM ti_ItemMixingInfo WHERE TargetItemNum IN (SELECT * FROM @allupdateditemIDs) ORDER BY TargetItemNum ASC

this one also without "," or this one:

这个也没有“,”或者这个:

    INSERT INTO @resulttable (UpdatedItemID, UpdatedItemName, NewProbability)
VALUES ((SELECT * FROM @allupdateditemIDs ORDER BY ItemNum ASC),
(SELECT ItemName FROM ti_Item WHERE ItemNum IN (SELECT * FROM @allupdateditemIDs) ORDER BY ItemNum ASC),
(SELECT MixingProbability FROM ti_ItemMixingInfo WHERE TargetItemNum IN (SELECT * FROM @allupdateditemIDs) ORDER BY TargetItemNum ASC))

I was already searching trough so many websites, but I wasn´t able to find anything useful because I don´t get my data for the table variable from 1 table, I get them from many.

我已经在搜索了很多网站,但是我找不到任何有用的东西,因为我从1个表中得不到表变量的数据,我从很多人那里得到它们。

I´d really appreciate your help. Thanks in advance ;)

我非常感谢你的帮助。提前致谢 ;)

2 个解决方案

#1

Try this one -

试试这个 -

INSERT INTO @resulttable 
(
      UpdatedItemID
    , UpdatedItemName
    , NewProbability
)
SELECT  
      t.ItemNum
    , tt.ItemName
    , tmi.MixingProbability
FROM @allupdateditemIDs t
JOIN dbo.ti_Item tt ON tt.ItemNum = t.ItemNum
JOIN dbo.ti_ItemMixingInfo tmi ON tmi.TargetItemNum = t.ItemNum
ORDER BY t.ItemNum

#2

I think you can join all three tables in your select query to get proper data like this:

我认为您可以在选择查询中加入所有三个表来获取这样的正确数据:

INSERT INTO @resulttable (UpdatedItemID, UpdatedItemName, NewProbability)   
    SELECT @allupdateditemIDs.ItemNum,ti_Item.ItemName,ti_ItemMixingInfo.MixingProbability 
    FROM @allupdateditemIDs Inner join ti_Item 
    ON ti_Item.ItemNum =@allupdateditemIDs.ItemNum inner join ti_ItemMixingInfo
    on ti_ItemMixingInfo.TargetItemNum=@allupdateditemIDs.ItemNum   
    ORDER BY @allupdateditemIDs.ItemNum ASC

#1