CODEWITHSUNDEEP
sql-servert-sqlsql-server-2008-r2
Krish
Krish

Reputation: 176

BCP from One Table to Another table in different servers in SQL Server 2008 R2

I have two different servers. I need to transfer the data from One Server table to another server table (both are on same structure) using either BCP or using BULK INSERT. I need to do this on the run time. Can anybody please help me on this? I have a linked share connection established between the servers. Looking for an option to copy data faster.

Upvotes: 3

Views: 4260

Answers (1)

sniperd
sniperd

Reputation: 5274

BCP will almost certainly the fastest, however it is not transactional and the error trapping is rather poor. It's pretty easy to use though, let's say you have server1 and server2 each having a the_database and the_table with the same structure.

  • From server1 go to your command line, start->run->cmd not Microsoft SQL Management Studio.
  • Type: bcp the_database.dbo.the_table out c:\the_data.txt -T -E
  • Just hit Enter on all the prompts
  • Wait for it, and copy the_data.txt to c:\ on server2
  • On server2 TRUNCATE the_table (unless you want to append, but watch for constraint problems)
  • On server2, once again go to command line
  • Type: bcp the_database.dbo.the_table in c:\the_data.txt -T -E
  • Magic! Your data has been imported

That will be your fastest solution. You can also use the IMPORT or EXPORT wizards inside of Microsoft SQL Management Server which is more of a GUI way to do the same thing, but the cmd way will be the fastest way to just copy massive amounts of data (millions of rows). If you simply type bcp from command line you'll see lots of options.

Nothing wrong with doing bulk insert across servers for but raw caveman power 'copy data fast' this is the fastest way to do it, imho.

Upvotes: 4

Related Questions