CODEWITHSUNDEEP
t-sqlsql-server-2008-r2
SkeetJon
SkeetJon

Reputation: 1491

How to insert into a table using results from a previous query

Good morning.

I am populating a many to many table using the results of a previous query as follows...

WITH Temp AS
(
    SELECT      UserID, Result 
    FROM        <Master_Table>
    WHERE       UserID IN (SELECT Name FROM Users)
)   
INSERT INTO UserDecisions 
    (User_Id, Decision_Id)
    VALUES
    (
        (SELECT Id FROM Users WHERE Temp.UserID = Users.Name),
        (SELECT Id FROM Decisions WHERE Temp.Result = Decisions.Name)
    )

On executing, I get the error as follows.

Msg 4104, Level 16, State 1, Line 22
The multi-part identifier "Temp.UserID" could not be bound.
Msg 4104, Level 16, State 1, Line 23
The multi-part identifier "Temp.Result" could not be bound.

How Would you resolve this please? Does the Temp subquery need to be a temporary table ?

Upvotes: 1

Views: 175

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239824

You need to write it as a SELECT rather than a VALUES clause:

WITH Temp AS
(
    SELECT      UserID, Result 
    FROM        <Master_Table>
    WHERE       UserID IN (SELECT Name FROM Users)
)   
INSERT INTO UserDecisions 
    (User_Id, Decision_Id)
    SELECT u.Id,d.Id
    FROM Temp t
    INNER JOIN Users u ON t.UserID = u.Name
    INNER JOIN Decisions d ON t.Result = d.Name

Upvotes: 3

Related Questions