Insert single row of values in a single column of a temp table

Question

Take this example query:

INSERT INTO #QueryOutput 
   SELECT DISTINCT 
      UR2.LeftID, UR3.LeftID  
   FROM 
      UserRelations UR1 
   JOIN 
      UserRelations UR2 ON UR1.RightID =  UR2.LeftID 
   JOIN 
     UserRelations UR3 ON UR2.RightID =  UR3.LeftID  
   WHERE 
     (UR1.RelationID = 1) 
     OR (UR1.RelationID = 1 AND UR2.RelationID = 1) 
     AND UR1.LeftID IN (SELECT UserID FROM #QueryInput)

With this being the magic row:

INSERT INTO #QueryOutput SELECT DISTINCT UR2.LeftID ,UR3.LeftID

#QueryOutput is a table with a single column, UserID

I want to insert UR2.LeftID, UR3.LeftID, or as many UR*.LeftID's I have in that single column. How do go about doing that?

Thanks

How are you going to fit two values into one column? Are you looking for a way to "pivot" the values, so that the multiple columns become multiple rows instead? — IMSoP
– IMSoP, Commented Jul 17, 2014 at 23:20
Yes, a way to pivot the values into a single column. I am just not good with SQL, and I don't fully understand pivoting :( — Evangelos Aktoudianakis
– Evangelos Aktoudianakis, Commented Jul 17, 2014 at 23:27
As written, your entire WHERE condition matches its first predicate, i.e. you could rewrite it as WHERE UR1.RelationID = 1 with same results. It's because X ∨ X ∧ whatever ≡ X. Something may be missing in the query's logic (or in the version you've posted here). — Andriy M
– Andriy M, Commented Jul 18, 2014 at 7:34

GarethD · Accepted Answer · 2014-07-18 00:31:31Z

2

If you are using SQL Server 2008 or later I think the best way to do this is to unpivot using cross apply and a table valued constructor:

INSERT INTO #QueryOutput 
SELECT  DISTINCT upvt.LeftID
FROM    UserRelations UR1 
        JOIN UserRelations UR2 
            ON UR1.RightID =  UR2.LeftID 
        JOIN UserRelations UR3 
            ON UR2.RightID =  UR3.LeftID
        CROSS APPLY (VALUES (UR2.LeftID), (UR3.LeftID )) AS upvt (LeftID)
WHERE   (UR1.RelationID = 1 ) 
OR      (UR1.RelationID = 1 AND UR2.RelationID = 1 ) 
AND     UR1.LeftID IN (SELECT UserID FROM #QueryInput)

answered Jul 18, 2014 at 0:31

GarethD

70k11 gold badges90 silver badges130 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2014-07-17 23:24:05Z

0

Perhaps this does what you want:

INSERT INTO #QueryOutput
    SELECT DISTINCT (case when n.n = 2 then UR2.LeftID 
                          when n.n = 3 then UR3.LeftID
                     end)
    FROM UserRelations UR1 JOIN
         UserRelations UR2
         ON UR1.RightID =  UR2.LeftID JOIN
         UserRelations UR3
         ON UR2.RightID =  UR3.LeftID CROSS JOIN
         (select 2 as n union all select 3) n 
    WHERE (UR1.RelationID = 1 ) OR (UR1.RelationID = 1 AND UR2.RelationID = 1 ) AND
          UR1.LeftID IN (SELECT UserID FROM #QueryInput);

answered Jul 17, 2014 at 23:24

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

2 Comments

Evangelos Aktoudianakis Over a year ago

I don't understand the .n. code, sorry. How does it work? If possible I would only like to change the first row of my query. The From and Where parts should remain as they are... Also, there may be more than just 2 values.I could have UR1.LeftID, UR2.LeftID, ... UR*.LeftID. I have no idea how that'd increment..Since I have to auto-generate the query in code :(

Gordon Linoff Over a year ago

This is doing an unpivot to get each value separately. I don't think you can do this just by changing the select clause.

Rahul · Accepted Answer · 2014-07-17 23:24:13Z

0

The way you are doing it's not possible except if you try doing a UNION ALL like below

INSERT INTO #QueryOutput

SELECT DISTINCT UR2.LeftID FROM UserRelations UR1 
JOIN UserRelations UR2 ON UR1.RightID =  UR2.LeftID 
JOIN UserRelations UR3 ON UR2.RightID =  UR3.LeftID  WHERE (UR1.RelationID = 1 ) 
OR (UR1.RelationID = 1 AND UR2.RelationID = 1 ) 
AND UR1.LeftID IN (SELECT UserID FROM #QueryInput)

UNION ALL

SELECT DISTINCT UR3.LeftID FROM UserRelations UR1 
JOIN UserRelations UR2 ON UR1.RightID =  UR2.LeftID 
JOIN UserRelations UR3 ON UR2.RightID =  UR3.LeftID  WHERE (UR1.RelationID = 1 ) 
OR (UR1.RelationID = 1 AND UR2.RelationID = 1 ) 
AND UR1.LeftID IN (SELECT UserID FROM #QueryInput)

answered Jul 17, 2014 at 23:24

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

2 Comments

Evangelos Aktoudianakis Over a year ago

Would this produce the exact same result as the table I suggested to start with? And can I use as many Union Alls I want for an arbitrary X amount of tables? Thank you.

Rahul Over a year ago

It should ... essentially you are pulling all the different LeftID into single column of temp table and yes you can use as many union all.

Collectives™ on Stack Overflow

Insert single row of values in a single column of a temp table

3 Answers 3

Comments

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related