Solved

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

Posted on 2009-03-31
3
227 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
  • 2
3 Comments
 
LVL 142

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

[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

Question has a verified solution.

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

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

910 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now