holemania
asked on
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you.
@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)