CODEWITHSUNDEEP
sql-servert-sql
Vinz
Vinz

Reputation: 3198

SQL Server - How can I insert select results from different tables in subquerys or table variables into a new table variable, in a stored procedure?

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.

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).

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.

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

Upvotes: 1

Views: 869

Answers (2)

Devart
Devart

Reputation: 121922

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

Upvotes: 2

Shahbaz Chishty
Shahbaz Chishty

Reputation: 502

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 [email protected] inner join ti_ItemMixingInfo
    on [email protected]   
    ORDER BY @allupdateditemIDs.ItemNum ASC

Upvotes: 1

Related Questions