Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2009-03-31
3
Medium Priority
?
243 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 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
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 UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

580 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