SQL Query - Stored Procedure Parameter with database

Hello experts,

I have mutiple databases that are the same scheme/structure, but different names.  I have a script that I want to copy some data from one database to another.  I need to specify which database is my target and which is my source.  I can change the query manually, but I would like to create a stored procedure where I can just specify the source and target database.  Below is an example.

DECLARE @SOURCE VARCHAR(10)
DECLARE @TARGET VARCHAR(10)
DECLARE @ID VARCHAR(30)

SET @ID = '12389'
SET @SOURCE = 'DB1'
SET @TARGET = 'DB2'

INSERT INTO @TARGET.DBO.CUSTOMER(ID, NAME, ADDRESS, CITY, STATE, ZIPCODE)
SELECT ID, NAME, ADDRESS, CITY, STATE, ZIPCODE FROM @SOURCE.DBO.CUSTOMER WHERE ID = @ID

I can't seem to get it to work.  Any ideas on what needs to be done so I can use @SOURCE and @TARGET?
holemaniaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

HainKurtSr. System AnalystCommented:
use dynamic sql


@sql varchar(max)

set @sql = "INSERT INTO " + @TARGET + ".DBO.CUSTOMER(ID, NAME, ADDRESS, CITY, STATE, ZIPCODE)
SELECT ID, NAME, ADDRESS, CITY, STATE, ZIPCODE FROM " + @SOURCE + ".DBO.CUSTOMER WHERE ID = " + @ID

exec (@sql)
0
HainKurtSr. System AnalystCommented:
try this one:

@sql varchar(max);

set @sql = 'INSERT INTO ' + @TARGET + '.DBO.CUSTOMER(ID, NAME, ADDRESS, CITY, STATE, ZIPCODE)
SELECT ID, NAME, ADDRESS, CITY, STATE, ZIPCODE FROM ' + @SOURCE + '.DBO.CUSTOMER WHERE ID = ''' + @ID + '''';

exec (@sql);
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
holemaniaAuthor Commented:
Thank you.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.