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


 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? 

7/31/2007 8:57:20 PM 29906 articles. 0 followers. Follow

5 Replies

Similar Articles

[PageSpeed] 11

 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.

Don't forget to click "Mark as Answer" on the post that helped you.
This credits that member, earns you a point and marks your thread as Resolved so we will all know you have been helped.
7/31/2007 10:31:31 PM

(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.
7/31/2007 11:24:59 PM

Thanks guys.  It's all working now. 

8/1/2007 4:28:38 PM


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?


6/1/2008 1:31:25 AM

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)

Regards, Sreenivas (Vasu) Chaparala
ps:Unless a question, this is an attempt to provide guidance. Turning this into a solution, if required, is ones own responsibility. Expecting the entire solution as-is is inappropriate for any professional.
6/1/2008 2:24:34 AM

Similar Artilces:

Copying information from One table to Another from 2 different databases on one server
Can I copy accross databases e.g.: In MS SQL I can: Insert into Database2..customer select * from Database1..customer Thanks in Advance Yes you can. The syntax in Sybase is same. The syntax is the same if you are on ASE. ASA does not support that syntax. You need to use a Proxy table instead. -- Jim Egan [TeamSybase] Houston, TX Sybase Developers Network ...

how can I copy the entire database from one sql server to another sql server?
How can I copy the entire database (all the objects from one database to another database)? thanks in advance. Use DTS (right click on a database in Enterprise Manager, select All Tasks, and then Import or Export, as appropriate). You can also back up the database and then restore on the other server.Starting with ASP.NET 2.0? Look at:Programming Microsoft Web Forms My Blog hi Douglas, Thanks for your post. I was looking at the Database Copy Wizard. But it says my local system does not have the permission to copy files over the network. I looked over some tuturials and changed ...

Copying one SQL Server Database into another
Hi, I'm trying to copy one SQL Server Database into another SQL Server DB within the same server. I learnt a bit about this copying, that it copies only the Database structure, the Tabels, constraints view stored procedures etc etc. Is there a tutorial where I can get clear instructions to do this. I just need the table structure without any data to be copied into this. Have even tried exploring creating and executing DTS packages but cudn't get much help with this. Any help wud be really appreciated. ThanksShravan Addaypally MCP You can use Enterprise Manager to create the script. Once...

Copy database from one SQL Server to SQL Server Compact edition
I have a SQL script that creates tables and have insert scripts to put data into it. I want to create the table in Compact SE for my mobile device project. I know I can run a script from VS using SMO, but is there some other way? I have SQL Server Express 2008 installed. From VS itself is there a way to create my tables with data in SQL Server Compact?Beware of bugs in the above code; I have only proved it correct, not tried it. (Donald Knuth)  In Studio 2008 you can use the Server Explorer to create a connection to an SQL Server 3.5 Compact Edition File, and run queries against it j...

import one table from one database to another database
if i have one table from database A, and i want to copy the table to database B, then how to do it in SQL server management studio express? is it i have to make sql query?Taj FreewarezNo limitations...It is not a shareware...It is simply free :) try      select * into databaseB.dbo.TableA            from databaseA.dbo.TableA or go to database B and run   select * into TableA              from databaseA.dbo.TableA...

Move a Table from one SQL 2005 Database to Another SQL 2005 Database
I created a table in an SQL 2005 database, however, I want to move that table and the data to another SQL 2005 database (within the same server). I am new to SQL 2005 and detailed instructions would be greatly appreciated. Thanks Bill You have two options the first is clean but the second is easier.  Try the links below for details.  Hope this helps.  Kind regards,Gift Peddie Thanks, but the documentations is somewhat overwhelming and was hoping of a ba...

copying tables from one database to another database
i am using visual web developer 2005 and SQL Express 2005 with VB  as the code behindi have two databases . i want to copy all the tables with all the contents from one database to another database programaticallyhow to achieve this ?please help me  easiest way is to use the detach - attach method or backup and restoreKH...

How to copy a table from one database to another database ?
Hello to you, I'm newbie using pb8.02 I'm uisng ms-access 2000 I'd database abc & xyz that contains customer table I want to copy some record on abc.customer to xyz.customer How ? What is the easy way ? should I using datawindow ? (i meant should I always had dw to got this purpose ? ) please advice steven DataWindow purists will flinch, but I think an embedded SQL INSERT INTO xyz.customer (SELECT ... FROM abc.customer) is the clearest way to do that. Two datawindows/datastores, retrieve the first, RowsCopy, Update on the second will also work. Pi...

How to copy table from one database to another database ?
Hello to you, I'm uisng pb8 How to copy some rows from customer table in (ABC database) to customer table in (XYZ databases) the selection row is : select * from customer where startdate = 2002-06-15 Please show me the code, step by step steven On Sun, 16 Jun 2002 22:30:37 -0400, steven wrote: >Hello to you, >I'm uisng pb8 > >How to copy some rows from customer table in (ABC database) to customer >table in (XYZ databases) > >the selection row is : >select * from customer where startdate = 2002-06-15 > >Please show me the code...

Copy values from one table to another table using LINQ to SQL?
Hello,I have two database tables that have different names but identical structure (columns).What is the easiest way to copy the values from one table to the other table using LINQ to SQL? Thanks,-- shawn shawn Hi, As far as I research, we could use "foreach"  to insert the data from one table to another table. The code is shown below.         //there exist two table list and listSecond        DataClassesDataContext dataClass = new DataClassesDataContext(); //create the instance of the DataCon...

how to copy a table from one database to another
Is there a way I can generate the "insert statements" along with the table structure when I click the "Generate SQL Script" in sql server 2000? So that way I can have the table structure and all the insert statement for my new database? first create the table by generating the script and then fire insert into newTableName select * from oldTableName is this what you want?Mark the post as answer if it helps you!Ashish Agrawal----------------------------MCPD(.NET Framework 2.0,Web Developer)MCTS(SQL Server 2005) Hi,avmood   U should Use DTS Wizard For that Her...

copying a table from one database to another
Hey in query analyzer, how do you copy a table form one db to another db i thort it was something like select * into dbo.databaseA.tableNew from dbo.databaseB.tableOld cheers  insert into databaseA..tableNew select * From databaseb..tableOld   The difference between SELECT INTO and INSERT INTO is that with INSERT the table must already exist. SELECT INTO creates a new table. Your original query looked okay, assuming that you wanted a new table tableNew. What error were you getting? You might also have a permissions problem since you are going from one database to a...

Copy table from one database to another
Hi, I need to create a exe, that will copy some columns from one table (present in DB1)  to another table (present in DB2). I need to do in C#. I got no idea from where to start. Can someone please help me out. Thanks in advance. I'm missing how this is Web related (Asp.Net)? Hi amp0201, You can execute this sql: SELECT * INTO TableTemp FROM Table  Thanks,Qin Dian TangMicrosoft Online Community SupportPlease remember to mark the replies as answers if they help and unmark them if they ...

Copy tables from one database to another?
Hello there, Is there an easy way to copy alle tables from one database to another?I have not really tried to create tables or copy or something like that, so i dont even know if its possible?Hope some of you can help me, ----------------Regards,Jeppe Richardt Hey you could use SQL Bulkcopy: Please: Don't forget to click "Mark as Answer" on the post that helped you. That way future readers will know which post solved your issue....

Web resources about - how to copy one table from one database to another on different servers? -

CES quick take: New Samsung and Lenovo laptops lighten the load
If you’ve been reading these blogs at all, you’ll know that I was recently worrying about which notebook to carry with me to CES. It had to be ...

Trials Set for Deputies Charged in La. Boy's Fatal Shooting
Judge sets separate trials for 2 deputy city marshals charged in boy's fatal shooting

Man Behind Armed Oregon Band Says He's on Mission From God
Like his father, man behind occupation of Oregon nature refuge says he is on mission from God

Saudi Foreign Minister to Iran Amid Diplomatic Crisis: 'Stop Being Aggressive' Saudi Foreign Minister to Iran Amid Diplomatic Crisis: 'Stop Being Aggressive' Saudi Arabia's foreign minister says ...

Netflix’s ‘Fuller House’ Tried to Get Elizabeth Olsen to Fill in for The Olsen Twins
We’ve known since last summer that Mary-Kate and Ashley Olsen would not be making an appearance on Netflix’s Full House revival series Fuller ...

'Making a Murderer' prosecutor admits 2 crucial mistakes in the case against Steven Avery
Former Calumet County prosecutor Ken Kratz hasn't wavered in his belief that Steve Avery and Brendan Dassey are guilty of murder. But he does ...

Twitter seems intent on increasing the character limit for tweets
Twitters 140-character limit for tweets could soon be a thing of the past. According to a new report from Re/Code , Twitter is planning to substantially ...

Five economic fears behind the Chinese stock slide that shook world markets
For the second time in less than six months, concerns about China’s rocky transition from an export-fueled industrial nation to a more mature ...

College Football Playoff Committee Drops the Ball on New Year's Eve
Year two of the College Football Playoff series took a beating, as the governing committee's refusal to shift the Orange Bowl and Cotton Bowl ...

Samsung is cooking with Wi-Fi and fake fire
LAS VEGAS — Modern cooks know how to use classic cooking implements to make 21st century delicacies. Not that there’s anything wrong with our ...

Resources last updated: 1/5/2016 10:38:28 PM