Limit for number of Id's in WHERE IN statement?

Databases
1

I need to pass in a large number of Ids for a WHERE IN statement and wanted to know if there are any limits/issues with this?

e.g.

SELECT * FROM [sometable] WHERE id IN (id1,id2,id3,id4)

The number of Ids could well be 1,000!

Your thoughts much appreciated :slight_smile:

2

much depends on which database system you’re running, which you neglected to mention

for example, in SQL Server, the size of a query string is limited to 65K

but of more concern is the sheer unwieldiness of this approach – where are these ids coming from?

the reason i ask is that there are likely better ways of doing whatever it is you’re trying to do

:slight_smile:

3

Thanks r937.

The Ids will be coming from another database (a flat file database system) that I have access to via an API. I am using MS SQL 2008 work group to perform the TSQL with the WHERE IN clause.

Basically there is a relationship between this flat file database and data stored in the MS SQL 2008 database. A query is performed on the flat file database and we show data in the MS SQL 2008 database.

Hope this makes sense?

4

why not import the flat file? it’d make things substantially simpler, and likely more efficient