SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Evangelist
    Join Date
    Jun 2001
    Location
    London
    Posts
    423
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,214
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist
    Join Date
    Jun 2001
    Location
    London
    Posts
    423
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,214
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    why not import the flat file? it'd make things substantially simpler, and likely more efficient
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •