CODEWITHSUNDEEP
sqlsql-serverstored-procedures
MTL323
MTL323

Reputation: 177

Insert records across multiple databases in stored procedure

I am attempting to create a stored procedure to insert records from a table in one DB to another DB, using fully qualified names for each table, however, since the tables can be in different DB's, the DB name needs to be able to change based on where a field is located. I attempted to set a variable and use the variable as the DB name, however, SQL does not allow this method.

Please supply me with any suggestions.

Below is a the query that I have to this point:

DECLARE @FromPractice varchar(5) = 'MCE'
DECLARE @ToPractice varchar(5) = 'CRRLL'
DECLARE @FromEnvironment varchar(5)
DECLARE @ToEnvironment varchar(5)

SET @FromEnvironment = ( select environment from practice where practice = @FromPractice)
SET @ToEnvironment = ( select environment from practice where practice = @ToPractice)


PRINT @FromEnvironment
Print @ToEnvironment

INSERT INTO [@ToEnvironment].dbo.Patinfo
(
 ---Fields
)
SELECT 
---Values
FROM [@FromEnvironment].dbo.PatInfo
where practice = @FromPractice
and pat_num = 25970

Upvotes: 1

Views: 3035

Answers (1)

Sean Lange
Sean Lange

Reputation: 33580

Here is a rough example of how you could do this in dynamic sql. Please note that this is vulnerable to sql injection. You need to add some logic in your process prior to this to make sure that you are safe. The easiest way given the nature of this is to check sys.databases for your environment variables. You are likely going to need those variable to hold more than 5 characters unless your database names are awfully short.

declare @SQL nvarchar(max)

set @SQL = 'INSERT INTO [' + @ToEnvironment + '].dbo.Patinfo
(
    --Fields
)
SELECT
--Values
FROM [' + @FromEnvironment + '].dbo.PatInfo
where practice = ''' + @FromPractice + '''
and pat_num = 25970'

--look before you execute
select @SQL

--When satisfied uncomment this
--exec sp_executesql @SQL

Upvotes: 1

Related Questions