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.

  • Modify binary(1) column in SSMS grid
  • Tsql ordering data by specific string values
  • Rounding SQL DateTime to midnight
  • Why i can not find value is numeric conditional in dqs domain rule?
  • Access to Sql Server via ODBC from C# : ERROR Data source name not found and no default driver specified
  • SQL to find first non-numeric character in a string
  • 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.