How to write large id's in SQL In ('','') format?

Question

I have a method which returns record from a database based on a id's. Now I need to fetch records for 1000 records and I feel like it is not a good practice to write all id's in the in clause manually. Is there a tool where I paste the id's and it gives me like this id in ('123', '456', and so on)?

I can't write all the id's by my self manually.Business provides the id's in excel and they are very large in number. is there a way to accomplish this?

My method

@Override
     public List<NYProgramTO> getLatestNYData() throws Exception {
    String query = "SELECT REQ_XMl, SESSIONID, EXPIRATION_DATE, QUOTE_DATE, POLICY_EFFECTIVE_DATE, TARGET_CREATED, RATING_TRANSACTION_ID, SOURCE_LASTMODIFIED FROM dbo.XML_SESSIONS with (nolock) WHERE XML_SESSIONS.LOB = 'PersonalAuto' AND XML_SESSIONS.RATING_STATE = 'NY' AND XML_SESSIONS.ID IN ('72742212', '71289432') ORDER BY XML_SESSIONS.SOURCE_LASTMODIFIED DESC";

    return this.sourceJdbcTemplate.query(query, (rs, rowNum) -> {
        NYProgramTO to = new NYProgramTO();
        to.setRequestXML(rs.getString("REQ_XML"));
        to.setSessionId(rs.getString("SESSIONID"));
        to.setExpirationDate(rs.getDate("EXPIRATION_DATE"));
        to.setQuoteDate(rs.getString("QUOTE_DATE"));
        to.setEffectiveDate(rs.getDate("POLICY_EFFECTIVE_DATE"));
        to.setCreatedDate(rs.getDate("TARGET_CREATED"));
        to.setRatingTransactionID(rs.getString("RATING_TRANSACTION_ID"));
        to.setSourceLastModified(rs.getTimestamp("SOURCE_LASTMODIFIED"));

        return to;
    });
}

Thanks

based on the under lying DB, the "IN" clause has limitation of setting the values (I think MySQL its 1000 ?). Suggest to get the records in batch. If the ids are in DB, read from DB and use in the query — Midhun, Mar 07 '18 at 20:43
Why *are* you writing all the ids manually? That's not normal, not with 2 values and not with 1000 values. I think your whole approach is flawed. — Kayaman, Mar 07 '18 at 20:45
I have the id's provided in an excel sheet by the business and for those id's I need to fetch records. — David, Mar 07 '18 at 21:11
Which [DBMS](https://en.wikipedia.org/wiki/DBMS) product are you using? Postgres? Oracle? "SQL" is just a query language, not the name of a specific database product. — , Mar 07 '18 at 21:44

score 0 · Answer 1 · answered Mar 07 '18 at 20:48

Correct me if I understood wrong. If you are looking for something which can give you concatenated value of a column separated with comma, then u should use excel. There you can paste your data in a column and apply concatenation function available in excel to get you result. You can refer This link

score 0 · Answer 2 · answered Mar 07 '18 at 20:55

Sounds like a rather simple java solution. Why don't you write a java method that takes the ids as a string parameter and returns a String formatted the way you need it.

Given you have all ids in one long character sequence/string you can simply do this:

public String toSQLIds(String pRawIds){
  String[] ids = pRawIds.split(Pattern.quote("SEPARATOR"));
  StringBuilder builder = new StringBuilder();
  builder.append("(");      
  for(int i = 0; i < ids.length; i++){
    builder.append("'").append(ids[i]).append("'");
    if(i < ids.length-1){
      builder.append(", ");
    }
  }
  builder.append(")");
  return builder.toString();
}

Something like that. Of course you need the correct separator to successfully split your ids up and format it the way you need it. Run this once with your ids and paste it into your sql.

I need to provide the id's. Id's are already present in the db. Suppose you want to fetch some record based on id's and you say id in ('1123', '987'). what if you had to insert 10000 id's in the in clause. — David, Mar 07 '18 at 21:03

How to write large id's in SQL In ('','') format?

2 Answers2