Solved

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

Posted on 2009-03-31
3
239 Views
Last Modified: 2013-12-07
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

0
Comment
Question by:pureauto
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 24034348
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
 

Author Comment

by:pureauto
ID: 24034816
That worked great.  Just had to add an apostrophe.
EXEC('INSERT INTO Records SELECT * FROM [' + @DatabaseName + '].tblAsiEFMHistoryRecords')

Open in new window

0
 

Author Closing Comment

by:pureauto
ID: 31565092
Thanks a bunch!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question