T-SQL: Where xxx IN temporary table

Question

I have a temp table and want to check in a where clause wether a certain id/string is contained in the temp table.

Select... WHERE MyId  IN MyTempTable 

I get a general error in MS SQL Management studio.

is the "In" operator not suited for temp tables?

Answer 1

Your syntax is wrong:

SELECT ...   FROM MyTable  WHERE MyID IN (SELECT MyID                   FROM MyTempTable) 

I don't much like the IN operator, so I prefer this:

SELECT ...   FROM MyTable  WHERE EXISTS (SELECT *                  FROM MyTempTable                 WHERE MyTable.MyID = MyID) 

But it's largely a matter of taste.