how to import data from other database in SQL Server 2005

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.

  • Identifying percentage in Fact Table
  • error when insert into linked server
  • passing Parameter to crystal report in windows form (vb.net )
  • query with join that count id of column and also count that id on behalf of particular value arrange both by year and month
  • ProgrammingError connecting to Azure using python pypyodbc
  • Using parameterised column filter and column sort direction
  • 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

    2 Solutions collect form web for “how to import data from other database in SQL Server 2005”

    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
    

    You need to specify all column names in query.

    insert into dbTest1.dbo.testData(col1,col2,col3) select * from dbTest2.dbo.testData
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.