How to import from multiple databases at once?

Question

I need to import data into a new database. The majority of it is stored in the database dbo_Orders, however i need to assign the data from it to the company that delivered the entry. This companyID is not stored in dbo_Orders but in dbo_Delivery. Luckily both databases note the OrderID so that i could compare them like a primary key (none of them have a primary key, im not responsible for database creation here, dont blame me please).

The main issue is that I also have to only import orders made by spefic companies stored in the array companyIDs(). This means that I will need to:

1. Select all entries from dbo_Delivery that are in companyIDs()
2. Take their OrderIDs and CompanyIDs
3. Select all entries from dbo_Orders where the OrderID is equal to the ones that i got from dbo_Delivery
4. Import them into the new DB and connect them to the CompanyID that deliverd the order

Is there any way to do this without having to make a huge array of all OrderIDs that i need to import. We are talking about 100.000 entries here.

Something like SELECT * WHERE [dbo_Orders].OrderID = "OrderIDs that are from specific companies"

(Again, im not responsible for the structure of these databases. It was my predecessor and i cannot change them for this task, so including CompanyID in dbo_Orders is not an option here)

Answer 1

have you tryed a LEFT JOIN

SELECT           *
FROM             dbo_Orders
LEFT JOIN        dbo_Delivery
ON               dbo_Orders.OrderID = dbo_Delivery.OrderID 
WHERE            CompanyID in (CompanyID1,CompanyID2,CompanyID3,CompanyID4,....) 

instead of making a list of the orderID from the companies you want, in this way you only make a list with the company's ID.

Good Luck Luis