How to get a data's from one database to other database?

Question

Using SQL 2000, SQL 2005

Old Database Name is – Sysdatabase
New Database Name is - Dual_Proone, Dual_Protwo

In the above two database table name and column name are different, but values are same.

For Example

Sysdatabase (Database Name)

Person (Table Name)

Column Names and values are

ID Date 

001 23-02-2009
002 24-02-2009

So on…,

Company (Table Name)

Column Names and Values are

Code Name

1001 Micorsoft
1002 Dell

So on..,

Dual_Proone (Database Name)

T_person (Table Name)

Column Names and values are

EmpID, Cardeventdate

001 23-02-2009
002 24-02-2009

So on..,

Dual_Protwo (Database Name)

T_Company (Table Name)

Column Names and values are

CoCode, CoName

1001 Micorsoft
1002 Dell

So on..,

From the above two database, Table and column are different, but values are same.

Here I want to take all the values from dual_Proone and dual_protwo to sysdatabase

My software is connecting with sysdatabase, but I need the values from dual_Proone, dual_Protwo database.

I cannot able to change the Table name and Column names in the software, I want to take a data’s from dual_Proone and dual_Protwo

Is Possible by using trigger?

Please can any one help to solve my problem

Need query Help.

Answer 1

In Sql Server 2005, it is possible to select from a different database useing

ServerName.DatabaseName.dbo.Table (if it is a different server, the server must be linked.)

So you can SELECT * FORM Dual_Proone.dbo.T_person as if the table was in the same database.

Have a look at this as well.