SQL Server : gather data from different databases

Question

I have many different application databases with a [Log] table. I have one central database with a similar log table, but with one extra column called TenantId. There is also a Tenant table with a TenantId and a DatabaseName column. These DatabaseName contain the names of the application databases.

Now I want to loop all the application databases and copy the log entries to the central log table, with the TenantId that belongs to the application database name.

Would it be possible to write one procedure in the central database instead of creating many procedures in the application databases? All databases are on the same SQL Server instance.

Answer 1

Just some quick Dynamic SQL. In the example below, CHINRUS is my central database and would therefore be excluded from consolidation.

I should add, that the WHERE should be tailored to exclude any misc database on the server. Yet another option would be to maintain a table which has the proper definitions.

Declare @LogTable  varchar(100)='[Chinrus].[dbo].[TransactionLog]'
Declare @CentralDB varchar(100)='Chinrus'

Declare @SQL varchar(max) = ''

Select @SQL = @SQL + SQL
 From (
        Select Name,SQL=';Insert Into '+@LogTable+' Select *,TenantId='''+Name+''' From ['+Name+'].[dbo].[TransactionLog] '
         From  master.dbo.sysdatabases 
         Where Name<>@CentralDB
      ) A

Select @SQL
--Exec(@SQL)