0

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.

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.

quarks
  • 33,478
  • 73
  • 290
  • 513
  • Bulk insert does not understand quoting/escaping of delimiters. Refer http://stackoverflow.com/questions/12902110/bulk-insert-correctly-quoted-csv-file-in-sql-server – iruvar Dec 11 '12 at 05:11

1 Answers1

0

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

cha
  • 10,301
  • 1
  • 18
  • 26