SQL Server 2008 R2 中不同服务器中从一张表到另一张表的 BCP

问题描述

我有两个不同的服务器.我需要使用 BCP 或使用 将数据从一个服务器表传输到另一个服务器表(两者都在相同的结构上)批量插入.我需要在运行时执行此操作.有人可以帮我吗?我在服务器之间建立了 linked share 连接.正在寻找一种可以更快地复制数据的选项.

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.

推荐答案

BCP 几乎可以肯定是最快的，但是它不是事务性的并且错误捕获相当差.不过它很容易使用，假设您有 server1 和 server2，每个都有一个 the_database 和 the_table相同的结构.

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.

• 从 server1 转到您的命令行，start->run->cmd 不是 Microsoft SQL Management Studio.
• 输入:bcp the_database.dbo.the_table out c:\the_data.txt -T -E
• 在所有提示中按 Enter 键
• 稍等片刻，将the_data.txt复制到server2
上的c:\
• 在 server2 TRUNCATE the_table(除非你想追加，但要注意约束问题)
• 在server2上，再次进入命令行
• 输入:bcp the_database.dbo.the_table in c:\the_data.txt -T -E
• 魔法！您的数据已导入

• 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

那将是您最快的解决方案.您还可以使用 Microsoft SQL 管理服务器中的 IMPORT 或 EXPORT 向导，这更像是一种 GUI 方式来做同样的事情，但 cmd 方式将是复制大量数据(数百万行)的最快方式.如果您只是从命令行输入 bcp，您会看到很多选项.

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.

这篇关于SQL Server 2008 R2 中不同服务器中从一张表到另一张表的 BCP的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！