I have 10+ SQL Server databases, from where I would use one table from each database to display information using the application that I am creating. For instance, DB1, DB2....DB10. NewDB (Account, Country, Costcenter....etc.).
I have started with creating a new database which would contain all the information from all those 10+ databases. However, I am confused in many cases.
First of all what process should I follow? Shall I create a table (in new database) with the same structure as the actual data source and insert data from actual data source to new database?
Should I be doing this on the DBMS? If so, is it some scripting? (hint expected since very new to this)
I am creating a report generation application which has 10+ data sources. I need some hint which way should I proceed?
Thanks for advice/help in advance.