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.

Another approach would be some dynamic SQL. Is this a one time event or will you schedule the consolidation? — John Cappelletti, Jul 24 '16 at 20:49

score 1 · Answer 1 · edited Jul 24 '16 at 21:09

1

You can get list of all databases with following query:

SELECT name 
FROM master.dbo.sysdatabases

and then you can use a cursor to get each database data and insert in one table in current database.

edited Jul 24 '16 at 21:09

marc_s

732,580
175
1,330
1,459

answered Jul 24 '16 at 20:52

Somayyeh Ataei

307
2
13

score 1 · Accepted Answer · answered Jul 24 '16 at 21:18

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)

SQL Server : gather data from different databases

2 Answers2