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?
 
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
 
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
 
holemaniaAuthor Commented:
Thank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.