I have two disconnected sql servers that have to have correlated queries run between them. What is the best way to run a query such as:
select * from table where id in (1..100000)
Where the 1..100000 are ids I'm getting from the other database and are not contiguous.
The in clause doesn't support that many parameters, and creating a temp table to do a subquery on takes forever. Are there any other options? Using Sql Server 2005 as the DB, C# as my lang.
Linking the servers is not an option.
Syntax might be off slightly, as my server to server MSSQL is rusty, but...
Select * from table where id in (select id from [Server_Two\Some_Instance].[SomeDatabase].[user].table2)
If possible, set them up as linked servers. Then you can query the other server directly.
Once you have your link setup, you should also consider that an INNER JOIN or EXISTS will likely perform better.
To work around the number of IN parameters allowed without querying across servers, you can bucket them into multiple queries with subsets of the ids and connect them with a UNION. Kinda kludgy, but it should work.
You could use a function to break down the input string and return a table. There are plenty of questions on here about how to have dynamic parameters with in clauses which should have an example.
If you can link your servers you could join between the two servers.
The other option which it sounds like you've explored is creating a temp table with the id's that are going to be used as criteria for a join to the primary table.
select * from atable a
inner join #temptable t on a.id = t.id
Since they're ID's I'm assuming they are indexed.
How are you generating the in? If it is text, you can generate it differently. Or does this cause the same error?
SELECT.....
WHERE id in (1..10000)
OR id in (10001..20000)
-- etc.