I’m really not sure what you are asking for here. Can you try to explain it again?

Hi Denny

This is like a backup strategy. Migrating to a new system. Before each application having individual DB but actually having only one table.

All databases having only one table with same schema. For example i have given EMP table.

Now we want to move data to a individual table(for example table “001″) in a single DB(DBbackup) from different DBs like DB001 and DB002, etc.

New table name will be like ’001′. This table refer to DB001 and will have data from EMP table from DB001.

DTS allowing only one DB at a time and there are more than 150 tables with millions of records. So this process will take long time. The whole application is accessed 24×7.

Can you please write me a script for this.

Thanks

Im posting again.

Hi

I have a db backup requirement. As shown in the attached image current system is as follows:

1. There are three source databases such as DB001, DB002 and DB003. Each DB having one table as dbo.EMP and same table schema.
2. Emp table consists of EmpID (PK) and EmpName.
3. One backup database i.e, DBbackup

I need as follows:

1. Looping through all DB: “SELECT NAME FROM sys.sysdatabases WHERE NAME LIKE ”DB00%””
2. Check if table dbo.EMP exist in each DB.
3. If exist then check table “001″ exist in DB “DBbackup”. >> “SELECT OBJECT_ID(‘DBbackup.dbo.001′,’U') AS Check ” . This query will return NULL if table “001″ not exist.
4. If not exist then create a table by taking part of DB name like “001″ with same schema in “DBbackup” database.
5. Select into “001″, from dbo.Emp before matching row count to avoid duplicate records.

Can you give idea how to achieve it.

Data export of each table is very time consuming. There are more than 150 DBs with huge data. Is there any alternative wizard or inbuilt script is there in SQL server 2005.

Hope now you understand.

Thanks

You can use SSIS in a FOR EACH loop using a .NET script in a Script Task to change the source database name, and change the destination table name.

You can use BCP from a batch file, using a text file with a list of database names and a FOR loop to process through the list of database exporting each one to a separate text file.

You can use a T/SQL loop like this.

EXEC sp_MSforeachdb 'IF EXISTS (SELECT * FROM ?.sys.tables WHERE name = 'Emp')
BEGIN
      IF NOT EXISTS (SELECT * FROM DBBackup.sys.tables WHERE name = '?')
                SELECT *
                INTO ?
                FROM ?.dbo.Emp
                WHERE 1=2
      TRUNCATE TABLE DBBackup.dbo.?

      INSERT INTO DBBackup.dbo.?
      SELECT *
      FROM ?.dbo.Emp
END'

You will probably be better off just backing up each database separately. You aren’t going to save anything by moving the data into a single database.