how to copy one table from one database to another on different servers?

Hello.

I need to copy all of the rows in a table from a database on one server, to another existing table of the same name in a different database on a different server. I'm trying to use a SELECT INTO statement. Any idea how to do this?

I've tried

SELECT * INTO DestinationServer.dbo.DestinationDB.DestinationTable
FROM SourceTable AS SourceTable_1

But this doesn't work, saying there are too many prefixes.

Any idea how to do this?

If you are using SQL2000, you use DTS to copy the table or SSIS if using SQL2005. Alternatively you can use OPENROWSET in the target database to read the table in the source database.

|||

(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.

|||

Thanks guys. It's all working now.

|||how to copy one table from one database to another on different servers?