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!

MT0
  • 143,790
  • 11
  • 59
  • 117
Senior Coder
  • 90
  • 7
  • 2
    I suggest to insert this values to a table ant then execute your queries – Sergey Mar 03 '21 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. – mao Mar 03 '21 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)` – Marc Guillot Mar 03 '21 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. – Thom A Mar 03 '21 at 14:17

1 Answers1

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)
eshirvana
  • 23,227
  • 3
  • 22
  • 38