1

I have to run a simply query like select * from table_a where col1 in (val1,val2, ..., valn) with a very large number of values of the order of tens of thousands. These values are extracted from logs and can be any alphanumeric string e.g. 001034abz10t

There is a limit on the number of values I can copy paste in to a query string in SQL Server Management Studio as a result I have to break the original set of values into smaller sets and run the query with each set.

Is there a way to do it with just one query either in SSMS or from the command line using sqlcmd or any other tool? with sqlcmd is there a limit on the length of a variable that can be passed to a script via the -v parameter. There will be one variable representing all the values val1, val2,...valn as a single string.

I have to run similar queries on DB2 and Oracle also so any info on doing it interactively or from the command line will be greatly appreciated!

Improve this question
4
  • 2
    I suggest to insert this values to a table ant then execute your queries Commented Mar 3, 2021 at 14:08
  • 4
    You quote three distinct RDBMS products in tags, but each has separate limitations on the total length of an SQL-statement and the number of items in an IN-list. For Db2 these limitations can be platform and version specific. In addition, the command-line-shell may have separate limitations on the total length of a command line. Use a better design, such as a temporary table to contain the in-list-values and a join. Commented Mar 3, 2021 at 14:11
  • I would run the query directly against the logs that provide those values. select * from table_a where col1 in (select value from Logs) Commented Mar 3, 2021 at 14:16
  • I have removed the tags here, as it really confuses the qusetion. If you want to run a statement in a specific dialect of SQL, you need to write a query relavent for that RDBMS; every dialect is different and none of them fully support ANSI SQL nor the same parts. If you're struggling translating a statement to a specific dialect, then ask about that dialect. Commented Mar 3, 2021 at 14:17

1 Answer 1

Reset to default
0

yes

  1. create a table and put your values in there

  2. then use :

    select * from table_a where col1 in ( select val from newtablename)
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

This can also be written as a join join newtablename on col1 = newtablename.val

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.