SQL Server copy data across databases

Question

I'm using SQL Server 2019. I have a "MasterDB" database that is exposed to GUI application. I'm also going to have as many as 40 user database like "User1DB", "User2DB", etc. And all these user databases will have "exact same" schema and tables.

I have a requirement to copy tables data (overwriting target) from one user database (let's say "User1DB") to the other (say "User2DB"). I need to do this from within "MasterDB" database since the GUI client app going to have access to only this database. How to handle this dynamic situation? I'd prefer static SQL (in form of Stored Procedures) rather than dynamic SQL.

Any suggestion will greatly be appreciated. Thanks.

question from:https://stackoverflow.com/questions/65946120/sql-server-copy-data-across-databases

Answer 1

Check out this question here for transferring data from one database to another.

Aside from that, I agree with @DaleK here. There is no real reason to have a database per user if we are making the assumption that a user is someone who is logging into your frontend app.

I can somewhat understand replicating your schema per customer if you are running some multi-billion record enterprise application where you physically have so much data per customer that it makes sense to split it up, but based on your question that doesn't seem to be the case.

So, if our assumptions are correct, you just need to have a user table, where your fields might be...

UserTable
UserId
FName
LName
EmailAddress
...

Edit: I see in the comments you are referring to "source control data" ... I suggest you study up on databases and how they're meant to be designed, implemented, and how data should be transacted. There are a ton of great articles and books out there on this with a simple Google search.

If you are looking to replicate your data for backup purposes, look into some data warehouse design principles, maybe creating a separate datastore in a different geographic region for that. The latter is a very complex subject to which I can't go over in this answer, but it sounds like that goes far beyond your current needs. My suggestion is to backtrack and hash out the needs for your application, while understanding some of the fundamentals of databases (or different methods of storing data). Implement something and then see where it can be expanded upon / refactored.

Beyond that, I can't be more detailed than the original question you posted. Hope this helps.