在批量插入的字段值中处理字段终止符

This is our code for inserting logs into the database:

这是我们将日志插入数据库的代码:

SET @SQL = 'BULK INSERT #tbl_Temp FROM '
        + ''''
        + @PathFileName
        + ''''
        + ' WITH (FIELDTERMINATOR ='
        + ' ''|''' 
        + ','
        + ' ROWTERMINATOR = '
        + ' ''|\n''' 
        + ','
        + ' CHECK_CONSTRAINTS, KEEPIDENTITY, MAXERRORS = 30)'

Basically, the log files we bulk insert are pipe character delimited, the above code works fine, however in the event that one field contains a pipe | in its value, for example a encrypted field, then this SQL code can't handle it.

基本上,我们批量插入的日志文件是管道字符分隔,上面的代码工作正常,但是如果一个字段包含管道|在其值中,例如加密字段,则此SQL代码无法处理它。

What are the ways to work around this kind of problem?

有哪些方法可以解决这类问题?

If BULK INSERT is not capable, then what should be the solution without modifying the log format as it is already in production.

如果BULK INSERT不具备,那么在不修改日志格式的情况下应该是什么解决方案,因为它已经在生产中。

1 个解决方案

#1

What we did was specifying a multi-character delimiter, such as '!@#$%^&*()', and it worked fine. But this is of course if you have a control on the software that generating log files

我们所做的是指定一个多字符分隔符,例如'!@#$%^&*()',它运行正常。但是,如果您对生成日志文件的软件有控制权,那当然是这样