Point At Different Databases

Question

Point At Different Databases

texpic

SSCertifiable

Points: 5885
More actions
March 19, 2008 at 11:52 am

#98804

I have a project that all the code is written like this:
INSERT INTO MyDataBase..TableOne
(exampleA)
SELECT
exampleB
FROM YourDatabase..TableOne
Lets say I wanted the flexibility to be able to rename MyDatabase and YourDatabase in an INI file, how would I need to accomplish this? Thanks.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

texpic SSCertifiable Points: 5885 More actions · Answer 1

I should add, I really don't want to pass the parameter from the application. Basically I'm thinking when I need to point this elesewhere, just edit a statement in the stored procedure.

Here is what I'm thinking but it doesn't work:

DECLARE @MyDatabase as varchar(100)

SELECT @MyDatabase = 'test'

INSERT INTO @MyDatabase.._test

(test)

SELECT

'dog'

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 2

You want to look up "Dynamic SQL" in Books Online. It will get you there.

The two big commands you want to look at are EXEC and sp_executeSQL.

Execute in BOL

Sp_executeSQL in BOL

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

texpic SSCertifiable Points: 5885 More actions · Answer 3

YIKES I'm lame!

Why not do this, makes it simple?

Set up a table in my database called "DatabaseNamed" with a field "MyDatabase" (I put the database name there I want to point to).

Then use something like this:

DECLARE @@UsedDatabase as varchar(100)

SELECT @@UsedDatabase = MyDatabase FROM DatabaseNamed

INSERT INTO @@UsedDatabase .._test

(test)

SELECT

'dog'

This does not work but the concept makes it easy to change the pointing of the database.

I tried:

INSERT INTO @@UsedDatabase .._test

INSERT INTO @@UsedDatabase + '.._test'

????

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

Because it doesn't work like. Follow Grant's advice and lookup "Dynamic SQL".

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]