Get Help:Ask a Question in our Forums|Report a Bug|More Help Resources
Last post Jun 01, 2008 02:24 AM by vasuvani
Jul 31, 2007 08:57 PM|LINK
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?
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?
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.
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.
Aug 01, 2007 04:28 PM|LINK
Thanks guys. It's all working now.
Jun 01, 2008 01:31 AM|LINK
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?
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)