T-SQL: Where xxx IN temporary table

I have a temp table and want to check in a where clause wether a certain id/string is contained in the temp table.开发者_JS百科

Select...
WHERE MyId  IN MyTempTable

I get a general error in MS SQL Management studio.

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

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.

Your syntax is slightly wrong. You need to do:

SELECT ...
  FROM ...
 WHERE MyId IN (SELECT MyId 
                  FROM MyTempTable);

in requires a list of specific items. Tables generally have more than one field, how is it to know which field you mean?

You can use a subquery, where field in (select whatever from #temp)