#StackBounty: #sql-server #sql-server-2016 #view #data-warehouse How to Develop Union Reports from Single Tenant Databases?

Bounty: 50

We have a database for each customer, for over 700 databases. All the schemas are the same. Now we want to conduct Reporting for : All Databases Customer Analysis. Many db professionals prefer single tenant databases. All database reside on the same server instance.
Single Tenant Database Discussion

CREATE TABLE CustomerOne.[dbo].[CustomerTransaction]
(
    [CustomerTransactionid] [int] identity(1,1) primary key NOT NULL,
    [Customerid] [int] NOT NULL default(1),
    [QuantityBought] [int] NULL,
)

Does anyone know of any other ideas not listed below, which would work?
Our idea is to either create large Views, however View performance is slow. Second option is stored procedures.

(1)

CREATE VIEW Reportingdb.dbo.CustomerTransaction
as
SELECT 
    [Customertransactionid]
    ,[Customerid]
    ,[Quantitybought]
FROM [CustomerOne].[dbo].[customertransaction]

UNION ALL

SELECT 
    [Customertransactionid]
    ,[Customerid]
    ,[Quantitybought]
FROM [CustomerTwo].[dbo].[customertransaction]

2) We can also create a stored procedure. Stored Procedure which will insert into this large table. However there will be a time lag between runs, we want instantaneous data.

insert into ReportingDB.[dbo].[customertransaction] 
select * from CustomerOne.[dbo].[customertransaction]
UNION ALL
select * from CustomerTwo.[dbo].[customertransaction]

SQL How to Convert Single Tenant Databases to a Multitenant Database


Get this bounty!!!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.