How do I select records from a variable number of different databases?

I am designing a database that is to be an aggregate of several proprietary databases (I cannot make changes to these).  The number of proprietary databases can vary.  

Here is the part I am having trouble with:
I am creating a stored procedure that will run periodically via a job to copy information from the proprietary database tables to mine.  How can I accomplish this without hardcoding the database names into the stored procedure?

Currently I am storing proprietary database names in a table called Target Databases.  I am not sure how to pull the DB name out of that table and use it in a select statement (see code below).  Is this possible?  or does someone have a better way to do this?

CREATE PROCEDURE ImportRecords 
	@DatabaseID int
AS
BEGIN
	SET NOCOUNT ON;
 
	DECLARE @DatabaseName nvarchar(50)
	SET @DatabaseName = (SELECT DatabaseName FROM TargetDatabases WHERE DatabaseID = @DatabaseID)
 
	INSERT INTO Records SELECT * FROM @DatabaseName.tblAsiEFMHistoryRecords
END
GO

Select allOpen in new window