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?