CODEWITHSUNDEEP
sqlsql-server
Derek
Derek

Reputation: 557

SQL Server : how to work around WHERE IN limit of 128 values

I am writing a query like this

SELECT *
FROM table
WHERE id IN (thousands of ids)

but this won't work because the limit of values for the WHERE IN is 128 on our version of SQL Server.

Is there a way to do this using a sub-query with a join, or maybe a temp table?

Any help is appreciated!

Upvotes: 2

Views: 268

Answers (3)

Sébastien Pertus
Sébastien Pertus

Reputation: 715

You should use a temporary table, in which you will insert all your ids, then make a inner join with your table

DECLARE @tmpTable TABLE
(
  Id int
)

Insert into @tmpTable (id) values (1), (2), (3)

SELECT *
FROM [table]
Inner join @tmpTable on [table].id = @tmpTable.id

Upvotes: 1

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

You can use VALUES in order to construct an in-line table containing all the values to be considered and then JOIN to this table:

SELECT t1.*
FROM table AS t1
INNER JOIN (VALUES (10), (20), (12), ... ) AS t2(id) 
ON t1.id = t2.id

Upvotes: 6

Serg
Serg

Reputation: 22811

Place them in temp table and 

SELECT *
FROM table
WHERE id IN (SELECT id from #Table_of_thousands_of_ids)

Upvotes: 3

Related Questions