MS SQL Server - 批量插入跨网络 [英] MS SQL Server - Bulk Insert Across a Network

问题描述

我有一个使用MS SQL Server的应用程序，我需要从文件中执行批量插入。关键是数据库和我的应用程序将托管在单独的服务器上。在网络上批量插入的最佳方式是什么？我到目前为止的两个想法：

1. 从应用服务器共享一个目录，db服务器可以找到，并使用远程文件中的批量插入语句进行导入




2. 从db服务器运行FTP服务器 - 执行导入时，只需ftp文件到db服务器，并使用批量插入从本地文件（我倾向于此选项）进行导入。

任何人都可以告诉我是否有更好的方法来做这个，或者如果没有，最有意义的，为什么？

解决方案

我之前做过，并尝试过两个选项。

最后，我做了选择1.的相反。应用程序可以找到的DB服务器。您不必在批量插入过程中处理带宽问题。

如果您特别关心安全性或可传输性，则FTP服务器选项可用。

最后一个选项（非常小心）是在本地化的SQL服务器上使用DTS。它可能更安全。如果你做错了，效率会低得多。

I have an application that uses MS SQL Server for which I'll need to do a bulk insert from a file. The sticking point is that the database and my application will be hosted on separate servers. What is the best way to do a bulk insert across a network? Two ideas I'd come up with so far:

1. From the app server, share a directory that the db server can find, and do the import using a bulk insert statement from the remote file

2. Run an FTP server from the db server - when the import is performed, simply ftp the file to the db server and do the import using a bulk insert from the local file (I am leaning towards this option).

Can anyone else tell me if there is a better way to do this, or if not, which one makes the most sense, and why?

解决方案

I've done it before, and tried both options.

In the end, I did the opposite of choice 1. Share a directory on the DB server that the app can find. You don't have to deal with bandwidth issues during the bulk insert.

The FTP server option works if you're particularly concerned with security or transferability.

A final option (be very careful) is to use DTS with a localized SQL server. It might be more secure. If you do it wrong, it'll be much less efficient.

这篇关于MS SQL Server - 批量插入跨网络的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！