(1) SELECT .. INTO is different from INSERT INTO SELECT.
SELECT INTO tries to create the table in the INTO clause and then inserts the data from the SELECT into the table.
INSERT INTO SELECT does not create the destination table. It will directly try to insert the result of the SELECT into the destination table.
(2) The naming convention you have "DestinationServer.dbo.DestinationDB.DestinationTable" is incorrect. its ServerName.Database.dbo.Table. You have it the other way.
Depending on whether you are executing this from source or destination you have to use appropriate 4-part name (basically for whichever (source/target) is remote).
(3) Spell out all the column names if you are using INSERT INTO SELECT.
***********************
Dinakar Nethi
Life is short. Enjoy it.
***********************
Marked as answer by Bo Chen – MSFT on Aug 03, 2007 06:15 AM
ndinakar
All-Star
49092 Points
6868 Posts
Moderator
MVP
Re: how to copy one table from one database to another on different servers?
Jul 31, 2007 11:24 PM|LINK
(1) SELECT .. INTO is different from INSERT INTO SELECT.
SELECT INTO tries to create the table in the INTO clause and then inserts the data from the SELECT into the table.
INSERT INTO SELECT does not create the destination table. It will directly try to insert the result of the SELECT into the destination table.
(2) The naming convention you have "DestinationServer.dbo.DestinationDB.DestinationTable" is incorrect. its ServerName.Database.dbo.Table. You have it the other way.
Depending on whether you are executing this from source or destination you have to use appropriate 4-part name (basically for whichever (source/target) is remote).
(3) Spell out all the column names if you are using INSERT INTO SELECT.
Dinakar Nethi
Life is short. Enjoy it.
***********************