Group IDs in SQL Server

Question

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.

could you select all the rows from oracle and filter them in your application? — Aprillion
– Aprillion, Commented Mar 16, 2012 at 18:00
@April, I am running the query it now. it takes about two hours to pull those records Ahww, ahww — Hung Captain
– Hung Captain, Commented Mar 16, 2012 at 18:04
well, that's what count(*) and where rownum < 1000 are for while testing something ;)) — Aprillion
– Aprillion, Commented Mar 16, 2012 at 18:17

Michael Fredrickson · Accepted Answer · 2012-03-16 18:10:23Z

1

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

edited Mar 16, 2012 at 18:10

answered Mar 16, 2012 at 17:53

Michael Fredrickson

37.5k6 gold badges95 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Hung Captain Over a year ago

Thanks Mike, I forget to mention, I dont have privilage to create tables in Oracle database.

Hung Captain Over a year ago

Thanks again Mike, I appreciate your quick help

Hung Captain Over a year ago

These linked server codes are boyond my experience... Still trying to get the job done though.

Aprillion · Accepted Answer · 2014-08-01 10:47:59Z

0

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';

edited Aug 1, 2014 at 10:47

Aprillion

22.4k6 gold badges59 silver badges94 bronze badges

answered Mar 16, 2012 at 18:01

MPelletier

16.8k18 gold badges91 silver badges140 bronze badges

Collectives™ on Stack Overflow

Group IDs in SQL Server

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related