We have a legacy application. For some reason, someone decided to create a database for each customer. So we have 500+ databases with the same schema.
I would like to convert into One multi-client database. The Original customer database does not have CustomerId. New Singular database does.
We are thinking of replacing old databases with Views, so the legacy application insert/update will still work.
Old Customer Database:
CREATE TABLE [CustomerOne].[dbo].[CustomerTransaction] ( [CustomerTransactionid] [int] identity(1,1) primary key NOT NULL, [QuantityBought] [int] NULL, )
New Reporting Database has CustomerId:
CREATE TABLE ReportingDB.[dbo].[CustomerTransaction] ( [CustomerTransactionid] [int] identity(1,1) primary key NOT NULL, [Customerid] [int] NOT NULL, [QuantityBought] [int] NULL, )
Replace Old Database with Views
create view [CustomerOne].[dbo].[CustomerTransaction] as select [CustomerTransactionid] ,1 as [CustomerId] ,[quantitybought] from ReportingDB.[dbo].[CustomerTransaction] where Customerid = 1 with check option
-- Attempt inserting into View insert into CustomerOne.dbo.Customertransaction (Quantitybought) values (4) Msg 515, Level 16, State 2, Line 1 Cannot insert the value NULL into column 'customerid', table 'ReportingDB.dbo.customertransaction'; column does not allow nulls. INSERT fails. The statement has been terminated.
How do I get the final statement to succeeed without a Trigger? Otherwise we will have to utilize some kind trigger to get it working. Maybe there is no way to easily obtain unified database.
We are trying to avoid changing the old application code (insert and update statements).