how to import data from other database in SQL Server 2005

Question

I have 2 databases in SQL Server 2005. I want a functionality that i have same table structure in 2 database for example i have a same table named as testData in 2 database named as dbTest1 and dbTest2.

Now i want a single query through which i can add all the records from table testData of database dbTest2 into table testData of database dbTest1.

I tried to use following query

insert into dbTest1.testData values select * from dbTest2.testData

but this query is not running and giving error.

I also tried

insert into dbTest1.testData(col1,col2,col3) values select * from dbTest2.testData but this also gives error that "Invalid object name dbTest2.testData"

Could any one help in this

Thanks

Answer 1

Replace dbTest2.testData with dbTest2..testData - you have to specify 3 things (or optionally leave the middle blank for dbo).

i.e.

insert into dbTest1..testData
select * from dbTest2..testData

If the table doesn't already exist in dbTest1, you can do this:

select * 
into dbTest1..testData
from dbTest2..testData