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.
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
2 Solutions collect form web for “how to import data from other database in SQL Server 2005”
dbTest2..testData – you have to specify 3 things (or optionally leave the middle blank for dbo).
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