Cross Database SQL Scripts

Question

I am working on a project where we speak directly from one database to another. We have the main database, where the client updates his website, and then the changes are transferred from one database to another through numerous SQL scripts. The core database acts as a preview site, allowing them to see the changes before pushing them to life.

We are currently encoding the name of the subordinate database directly in SQL queries.

eg. INSERT INTO [slave-database] .dbo.TableName

This is an absolute pain, however, branching out a development where we will have several sets of two databases. Any stored development-related processes are updated to point to a subordinate database for this particular thread, for example. [Branch5 slave database].

This is not ideal, since changes must be manually changed when pressed in the body (changing the names of hard-coded databases).

Is there a better way to do this? The only solution I can see at the moment is to create an instance of SQL Server for each branch, which means that the database names can always remain the same in all branches. That would be an absolute pain, although it would be quite overhead. Is it possible to set a constant for a database name in SQL Server? Allows you to simply update a single value in the branch database. Dynamic SQL is out of the question.

Answer 1

Take a look at using the unregistered system stored procedure sp_MSforeachdb

sp_MSforeachdb

. , .

Answer 2

. , SQL.

"" , . . , , "".

- , . .

Answer 3

Sql . :

declare @query nvarchar(4000)
set @query = 'INSERT INTO [' + @slavedbname + '].dbo.TableName ...'
exec sp_executesql @query

:

select @slavedbname = value from Settings where name = 'SlaveDbName'

, _slave:

select @slavedbname = db_name() + '_slave'

Answer 4

Sql ( @Andomar), SQL.

DDL . , SQLCmd, script (. SQLCmd doco). / script, . , .

Ultimately, the best answer may be to transfer content from the database as a whole and transfer it to the application layer. This, as a rule, is a cleaner approach and leads to additional advantages, since the location of the two databases is no longer relevant (for example, they can be on separate servers). Only you can decide if this is redundant for your needs.