Given:
无法在 SQL Server 外部对该文件进行预处理
Option 1
为什么不使用OPENROWSET(批量...) http://msdn.microsoft.com/en-us/library/ms190312.aspx?这将允许您导入/插入(处理行终止符),同时分割(处理字段终止符)。取决于您是否可以创建格式化文件 http://msdn.microsoft.com/en-us/library/ms191516.aspx,它应该类似于以下内容之一:
格式化文件=分割每一行
INSERT INTO dbo.TableName (ColumnName)
SELECT split.SplitVal
FROM OPENROWSET(BULK 'path\to\file.txt',
FORMATFILE='Path\to\FormatFile.XML') data(eachrows)
CROSS APPLY SQL#.String_Split(data.eachrow, N' ', 2) split;
无格式文件 = 将整个文件拆分为一行
INSERT INTO dbo.TableName (ColumnName)
SELECT split.SplitVal
FROM OPENROWSET(BULK 'path\to\file.txt', SINGLE_CLOB) data(allrows)
CROSS APPLY SQL#.String_Split(
REPLACE(data.allrows, NCHAR(10), N' '),
N' ',
2 -- remove empty entries
) split;
Notes:
对于这两种方法,您都需要使用字符串拆分器。基于 SQLCLR 的拆分器是最快的,在上面的示例中,我使用了SQL# http://www.SQLsharp.com/库(我创建的但String_Split
该功能在免费版本中可用)。您也可以自己编写。如果您自己编写并且是not使用格式文件,允许多个分割字符可能是一个好主意,这样您就可以传入“”和“\n”并摆脱REPLACE()
.
-
如果您可以编写自己的 SQLCLR 字符串拆分器,那么编写一个接受输入参数的 SQLCLR 存储过程可能会更好@FilePath
,读取文件,进行分割,并吐出单列中的多行单词:
INSERT INTO dbo.TableName(ColumnName)
EXEC dbo.MySQLCLRproc(N'C:\path\to\file.txt');
如果您不使用(或无法使用)格式文件,请务必使用正确的“SINGLE_”选项,因为您可以执行以下任一操作SINGLE_CLOB
(返回VARCHAR(MAX)
对于标准 ASCII 文件)或SINGLE_NCLOB
(返回NVARCHAR(MAX)
对于 Unicode 文件)。
即使您可以创建格式文件,它might根据文件的大小,将整个文件作为单个字符串提取会更有效,因为分割一个大字符串可以相当快地完成,并且将是单个函数调用,而包含数千个短行的文件将是数千个函数调用也很快,但可能不会比单个调用快 1000 倍。但如果文件为 1 MB 或更大,那么我可能仍然会选择执行格式化文件并处理尽可能多的短行。
Option 2
如果“预处理”指的是更改,但不限制简单地读取它们并从 SQL Server 外部插入数据,那么您应该编写一个小型 .NET 应用程序来读取行、分割行,并通过调用接受表值参数 (TVP) 的存储过程来插入数据。我在 S.O. 的另一个答案中详细介绍了这种方法:
如何在最短的时间内插入1000万条记录? https://stackoverflow.com/questions/25770180/how-i-can-insert-10-million-entry-in-shortest-time-possible/25773471#25773471
这可以编译为控制台应用程序并批量使用(即.CMD / .BAT)脚本,甚至安排为 Windows 任务。