CODEWITHSUNDEEP
sql
Hung Captain
Hung Captain

Reputation: 97

Group IDs in SQL Server

Hope all is well. I have a table with about 491,000 unique records in a SQL server database. I need to run these records against another table in Oracle database. The problem I have in Oracle is it can on filter 1000 records at a time. Here is the query and its error:

SELECT *
FROM TABLE1
WHERE ID IN(A LIST OF 1001 RECORDS)

[Error] Script lines: 1-4 -------------------------- ORA-01795: maximum number of expressions in a list is 1000 Script line 3, statement line 3, column 11017

I can use the following query to run for couple thousand records but not sure how to do it with 491000 records.

SELECT ID
FROM TABLE2
WHERE (  ID IN(LIST OF 1000 RECORDS)
OR ID IN(LIST OF ANOTHER 1000 RECORDS)
OR ID IN(LIST OF ANOTHER 1000 RECORDS)
OR ID IN(LIST OF ANOTHER 1000 RECORDS))

Could anyone helpe me this this problem?

Many thanks

Forget to mention, I do not have privillage to create tables in Oracle database.

Upvotes: 0

Views: 132

Answers (2)

MPelletier
MPelletier

Reputation: 16687

As @Aprillion mentionned, normally there would be a pattern for which ID's are relevant to fetch.

Suppose you have ID's for where the name is 'Bob', do this:

SELECT * 
FROM TABLE1
WHERE name = 'Bob'

If name is in another table, use a subquery:

SELECT *
FROM TABLE1
WHERE ID IN (SELECT ID FROM TABLE2 WHERE name = 'Bob');

Or a join:

SELECT TABLE1.*
FROM TABLE1 INNER JOIN TABLE2 ON TABLE1.ID=TABLE2.ID
WHERE name = 'Bob';

Upvotes: 0

Michael Fredrickson
Michael Fredrickson

Reputation: 37388

EDIT:

Since you can't create tables in your Oracle database, have you considered using a Linked Server to join straight from your SQL Server table to your Oracle table?

How to set up and troubleshoot a linked server to an Oracle database in SQL Server

You could create the linked server using code similar to:

-- Adding linked server (from SQL Server Books Online):
/* sp_addlinkedserver [@server =] 'server' 
    [, [@srvproduct =] 'product_name']
    [, [@provider =] 'provider_name'] 
    [, [@datasrc =] 'data_source'] 
    [, [@location =] 'location'] [, [@provstr =] 'provider_string'] 
    [, [@catalog =] 'catalog']
*/ 

EXEC sp_addlinkedserver   'Ora817Link',  'Oracle',  'MSDAORA',  'oracle817'

-- Adding linked server login:
/* sp_addlinkedsrvlogin [@rmtsrvname =] 'rmtsrvname'
    [,[@useself =] 'useself']
    [,[@locallogin =] 'locallogin']
    [,[@rmtuser =] 'rmtuser']
    [,[@rmtpassword =] 'rmtpassword']
*/ 

EXEC sp_addlinkedsrvlogin 'Ora817Link', 'FALSE',NULL, 'scott', 'tiger'

Which would then allow you to perform a simple join between the two tables that reside on two different servers:

SELECT ot.ID
FROM 
    YourOracleServer...OracleTable ot
    JOIN SQLServerTable st ON ot.ID = st.ID

Upvotes: 1

Related Questions