copy data from one table to another table on different server

Question

copy data from one table to another table on different server

amanmeet.mehta 46966

SSC Eights!

Points: 859
More actions
September 30, 2011 at 5:37 pm

#390520

We need to copy data from one table to another table but the other table is on a different server.
Please let me know in case any one has any idea about it.
Help will be appreciated

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

SQL Stud SSC Eights! Points: 987 More actions · Answer 1

Depending on how much data you have, I would try the import export wizard as it is the easiest way. So in SSMS, right click on the database, go to tasks > Import Data and follow the on screen prompts.

http://msdn.microsoft.com/en-us/library/ms141209.aspx

If you find you have ALOT of data, the old faithful BCP but it's not for the faint of heart.

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 2

Do you have the option to use SSIS?

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

vince_sql Right there with Babe Points: 722 More actions · Answer 3

amanmeet.mehta 46966 (9/30/2011)
We need to copy data from one table to another table but the other table is on a different server.
Please let me know in case any one has any idea about it.
Help will be appreciated

Need more details?

Is this a one-off job or will it need to be done regularly?

How different in structure are the two tables?

You could export the data as a CSV, import it into a temporary table on the server then select into your target table.

http://sqlvince.blogspot.com/[/url]

Andre Guerreiro SSCertifiable Points: 7319 More actions · Answer 4

How about registering a linked server?

Then you could do something like this:

INSERT INTO LocalServerName.DatabaseName.SchemaName.TableName(<columns>)

SELECT <columns> FROM LinkedServerName.DatabaseName.SchemaName.TableName;

More information here: http://msdn.microsoft.com/en-us/library/ms188279.aspx

And here: http://msdn.microsoft.com/en-us/library/aa213778(v=sql.80).aspx

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA

amanmeet.mehta 46966 SSC Eights! Points: 859 More actions · Answer 5

amanmeet.mehta 46966

SSC Eights!

Points: 859

October 2, 2011 at 7:31 pm

#1389259

yes we have option of ssis.

prithvi.m Mr or Mrs. 500 Points: 561 More actions · Answer 6

Linked server will serve you better. But as mentioned by others before, if the data is large, then it would be advisable to do import export.

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 7

Is the data relatively simple? Meaning easily exported to a flat file? If so, BCP is your best (and probably fastest bet). If not, create the linked server as previously mentioned and use SSIS to copy the data across...depending on your server hardware (and network bandwidth), you should have no trouble copying the data across.

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 8

Many options:

Linked Server

BCP

Export Import Wizard

SSIS

rams.prsk Ten Centuries Points: 1241 More actions · Answer 9

Depending on how much data you have, I would try the import export wizard as it is the easiest way. So in SSMS, right click on the database, go to tasks > Import Data and follow the on screen prompts.

SQL Stud SSC Eights! Points: 987 More actions · Answer 10

The nice thing about the import export wizard is you can save your job as an SSIS package for future work if you would need this to be a repeateable process.