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

Open in new window

pureautoAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
this would do:
CREATE PROCEDURE ImportRecords 
      @DatabaseID int
AS
BEGIN
      SET NOCOUNT ON;
 
      DECLARE @DatabaseName nvarchar(50)
      SELECT @DatabaseName = DatabaseName FROM TargetDatabases WHERE DatabaseID = @DatabaseID
      EXEC('INSERT INTO Records SELECT * FROM [' + @DatabaseName + ].tblAsiEFMHistoryRecords')
END
GO

Open in new window

0
 
pureautoAuthor Commented:
That worked great.  Just had to add an apostrophe.
EXEC('INSERT INTO Records SELECT * FROM [' + @DatabaseName + '].tblAsiEFMHistoryRecords')

Open in new window

0
 
pureautoAuthor Commented:
Thanks a bunch!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.