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
SET NOCOUNT ON;
DECLARE @DatabaseName nvarchar(50)
SET @DatabaseName = (SELECT DatabaseName FROM TargetDatabases WHERE DatabaseID = @DatabaseID)
INSERT INTO Records SELECT * FROM @DatabaseName.tblAsiEFMHistoryRecords