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.
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.
Click "Mark as Answer" on the post that helped you.
This earns you a point and marks your thread as Resolved so we will all know you have been helped.
FAQ on the correct forum http://forums.asp.net/p/1337412/2699239.aspx#2699239
(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
Thanks....this post was especially helpful. I ended up using the SELECT INTO, because using INSERT INTO SELECT gave me issues with primary key duplication.....would you know why?
SELECT * INTO does not work with existing destination table. It will work if the destination table does not exist and creates one when you run the query
Also, schema should always be after db name (not before)
Please note that, if the destination table does not exist, trying to use select * into remoteserver.db.schema.table from .. does NOT work. Only insert into ... select from works (means, table should already exist)
Other way of doing it is to use DTS or export/import wizard that comes with sql 2000/2005.
You can have this for sql 2005 express also by installing SQL Server Tookkit. once installed, simply got start->run: type dtswizard., it will be launched
other ways are to export/import the data via flat file (using bcp / select insert etc)
bhbase99
0 Points
5 Posts
how to copy one table from one database to another on different servers?
Jul 31, 2007 08:57 PM|LINK
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?
TATWORTH
All-Star
72405 Points
14018 Posts
MVP
Re: how to copy one table from one database to another on different servers?
Jul 31, 2007 10:31 PM|LINK
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.
This earns you a point and marks your thread as Resolved so we will all know you have been helped.
FAQ on the correct forum http://forums.asp.net/p/1337412/2699239.aspx#2699239
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.
***********************
bhbase99
0 Points
5 Posts
Re: how to copy one table from one database to another on different servers?
Aug 01, 2007 04:28 PM|LINK
Thanks guys. It's all working now.
Monster
Member
178 Points
45 Posts
Re: how to copy one table from one database to another on different servers?
Jun 01, 2008 01:31 AM|LINK
Dinakar,
Thanks....this post was especially helpful. I ended up using the SELECT INTO, because using INSERT INTO SELECT gave me issues with primary key duplication.....would you know why?
Kim
vasuvani
Contributor
2708 Points
413 Posts
Re: how to copy one table from one database to another on different servers?
Jun 01, 2008 02:24 AM|LINK
SELECT * INTO does not work with existing destination table. It will work if the destination table does not exist and creates one when you run the query
Also, schema should always be after db name (not before)
Please note that, if the destination table does not exist, trying to use select * into remoteserver.db.schema.table from .. does NOT work. Only insert into ... select from works (means, table should already exist)
Other way of doing it is to use DTS or export/import wizard that comes with sql 2000/2005.
You can have this for sql 2005 express also by installing SQL Server Tookkit. once installed, simply got start->run: type dtswizard., it will be launched
other ways are to export/import the data via flat file (using bcp / select insert etc)
http://www.chaparala.com