Setting a variable for database name

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Setting a variable for database name

Author

Topic

Sparky_
Starting Member

6 Posts

Posted - 2012-04-17 : 09:30:44

Greetings,

I have a set of scripts that I now run before importing new data into our main database that checks for a couple of conditions that have caused me serious grief.

What I have is a line reading:
FROM [XYZ].[dbo].[_Table1]

With each newly received database I have to go and cut/paste a new name in 24+ different locations.

How can I have something like the following:

A variable called: received_database_name
Then I would have the line below at the top and set the new name one time:

Received_database_name = XYZ

Throughout my scripts I would have:

FROM [Received_databse_name].[dbo].[_Table1]

Thanks
-Sparky

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-17 : 09:44:10

Two possibilities that I can think of, and personally I would prefer option #1 below.

1. Run all your queries/scripts from the imported database and explicitly refer to the main database in the scripts, do not refer explicitly to the imported database. Something like this:

USE XYZDatabase
GO

INSERT INTO MainDatabase.dbo.Table1
SELECT * FROM dbo.Table1;

2. Use dynamic SQL, and run it from the main database:

DECLARE @sql NVARCHAR(4000);
DECLARE @databasename NVARCHAR(255);
SET @databasename = 'XYZDatabase';
SET @sql = 'INSERT INTO dbo.Table1 SELECT * FROM ' + QUOTENAME(@databasename) + '.dbo.Table1';

sp_executesql @sql;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources