CODEWITHSUNDEEP
sql-serverssissql-server-2008-r2
svbyogibear
svbyogibear

Reputation: 343

Where Clause And Select in Two Separate DBs on to separate servers

Just to state, currently making use of a linked server connection is not possible as I do not have access to change anything on one of the servers. With that in mind, is it possible to do the following:

SELECT * FROM TableA
WHERE ID IN(SELECT ID FROM TableB)

WHERE Table B is on my server and Table A is on another server. I was thinking about making use of an OLE DB Source with a conditional split whereby the the

SELECT * FROM TableA

would stem from the other database on the server I don't have access to change and the

WHERE ID IN(SELECT ID FROM TableB)

in the form of a conditional split. How would I approach this?

Upvotes: 0

Views: 53

Answers (1)

Neil P
Neil P

Reputation: 3190

You question isn't exactly clear, but I think you are wanting to use SSIS to extract data where the id is in a table on another database.

To do this, I would add a source for each item, then sort the items by the primary / foreign key (you can do this in your source query and set the "is sorted" property to do this more efficiently, but it's a bit more tricky). Then you can use the merge join component to join the two tables together using an inner join so you only get IDs that are in both tablesenter image description here

Edit:

To tune this for best performance, change your key lookup source to have the following query:

SELECT DISTINCT ID FROM TableB order by ID

Set the "is sorted" property on this source and remove the sort block that follows. (you can add order by and remove the sort from the other source as well - ONLY REMOVE THE SORT BLOCK IF THE SOURCE HAS BEEN SORTED ON THE ID)

The merge sort required both sides to be sorted on the same key.

Upvotes: 1

Related Questions