SQL 打开和读取文本文件的存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4425001/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Stored Procedure to Open and Read a text file
提问by Cyber Slueth Omega
I am looking for a stored procedure code that will open a text file, read in several thousand lines, and add the code to a table in the database. Is there a simple way to implement this in T-SQL?
我正在寻找一个存储过程代码，它将打开一个文本文件，读取数千行，并将代码添加到数据库中的表中。有没有一种简单的方法可以在 T-SQL 中实现它？
采纳答案by brian
If the file is ready to load "as-is" (no data transformations or complex mappings required), you can use the Bulk Insert command:
如果文件已准备好“按原样”加载（不需要数据转换或复杂的映射），您可以使用批量插入命令：
CREATE PROC dbo.uspImportTextFile
CREATE PROC dbo.uspImportTextFile
AS
AS
BULK INSERT Tablename FROM 'C:\ImportFile.txt'
WITH ( FIELDTERMINATOR ='|', FIRSTROW = 2 )
BULK INSERT Tablename FROM 'C:\ImportFile.txt'
WITH ( FIELDTERMINATOR ='|', FIRSTROW = 2 )
回答by Abe Miessler
I would recommend looking at using SSIS. It's designed to do this sort of thing (especially if you need to do it on a regular basis).
我建议考虑使用 SSIS。它被设计用来做这种事情（特别是如果你需要定期做的话）。
Here is a good linkthat goes over reading a text file and inserting into the DB.
这是一个很好的链接，可以阅读文本文件并插入到数据库中。
回答by Justin
The most efficient way of inserting many records into a table is to use BULK INSERT(I believe that this is what the BCP Utilityuses, and so it should be just as fast).
将许多记录插入到表中的最有效方法是使用BULK INSERT（我相信这是BCP 实用程序使用的，因此它应该同样快）。
BULK INSERTis optimised for inserting large quantities of data and is intended to be used when the performance of a simple INSERTstatement simply won't do.
BULK INSERT针对插入大量数据进行了优化，旨在用于简单INSERT语句的性能根本无法实现的情况。
If BULK INSERTisn't what you are after then you might want to take a look at the following article for a more straightforward technique:
如果BULK INSERT不是您所追求的，那么您可能需要查看以下文章以获得更直接的技术：
Linked in the article is a stored procedure uftReadFileAsTablewhich seems like it should be versatile enough to achieve what you are after.
文章中链接的是一个存储过程uftReadFileAsTable，它看起来应该足够通用以实现您所追求的目标。
If it isn't then you can at least use the stored procedure as an example of how to read files in SQL (it uses OLE / the Scripting.FileSystemObject)
如果不是，那么您至少可以使用存储过程作为如何在 SQL 中读取文件的示例（它使用 OLE / Scripting.FileSystemObject）
