Update & Merge MultiDB into Single DB - Do this as a Routine - Weekly

[ruthboaz]

Hi,

I need to merge about 50 database into a single database for reporting purpose.
All have the same structure.

I do not know at any point of time the list of database in the server. It needs to be run as a routine every week, if possible automatic update.

How do I do this?

I am working in SQL Server 2000 with Access as front end.

Is there a way to know the list of database in a server thro a storedprocedure.

DTS I see, transfer is done only one database at a time, but I can create a routine.

If I do as a storedprocedure how do know what is list of database thats in the server or how do I run the storedprocedure automatically to execute weekly.

Pls advice.

Thanks & Regards,
Ruth.

[Motoma]

I'm not entirely sure this is necessary.
You can perform cross database joins/unions in SQL Server. I am sure this would be a lot quicker and safer than merging all of your databases into one.
If you must, could you use osql to populate a list of databases, and run a SELECT [Name] FROM sysdatabases to find the databases, and do a merge with that list.

[ruthboaz]

I'm not entirely sure this is necessary.
You can perform cross database joins/unions in SQL Server. I am sure this would be a lot quicker and safer than merging all of your databases into one.
If you must, could you use osql to populate a list of databases, and run a SELECT [Name] FROM sysdatabases to find the databases, and do a merge with that list.

Thank you..

Regards,
Ruth.