Move table from 1 db to another?

[RichS]

Hi folks,
How do I move the data from one table in a database to another table in a different database? I found the following code on this site but my field names are not the same in the two tables so I can't use the '*':

Insert into database2.dbo.table2 select * from database1.dbo.table1

any help would be greatly appreciated.

Rich

 

[balves]

If you're using V7 or 2000, you would be better off using DTS.

 

[RichS]

Thanks for the reply. I am using 2000 and am still very new to DTS. I looked at mapping the fields using the "Use query to specify data to transfer" option but did not see an option for this. Is there a tab in DTS where you can map the fields directly between tables?

Thanks,
Rich

 

[MinAmiChief]

Try:

select *
into database2.dbo.table2
from database1.dbo.table1

Creates a new table in database2. You'll have to move the indexs across another way.
Between servers:
Use Open rowset.
Select *
into Db2.dbo.NewTable
FROM OPENROWSET('SQLOLEDB','TESTSRV2';'DFB';'DFB',
'SELECT * FROM pubs.dbo.authors ') AS a

OR

Select *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'D:\TEMP\Qrysmp97.mdb';
'Admin';
'',
Employees) Be the change that you want to see in the world - Mahatma Gandhi

 

[sloat]

Insert into database2.dbo.table2 select * from database1.dbo.table1

did you try adding explicit fields

Insert into database2.dbo.table2 (field1, field2, ...) select (fieldxz,fieldpdq,myfield)
from database1.dbo.table1

this is common for insert with imbedded select within one database and should be for you.

 

[RichS]

Yes i did but i got an error - tried it again and it worked. i must have had a syntax problem before.

Thanks very much everyone.

Rich