CODEWITHSUNDEEP
sql-serverbcp
DC07
DC07

Reputation: 303

BCP - Include terminator in data insert

I'm using BCP to load a json file to SQL Server (yes I know there are better ways, but need to try this)

The problem is, the json document is not formed properly because the terminator in the format file is being removed, but I want it included

bcp db.dbo.test IN G:\JSON\json.out  -f G:\JSON\formatfile.out -T

format file terminator:

  <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="] }" COLLATION="Latin1_General_CI_AI"/>

How can I end the file without truncating the json closing tags?

Upvotes: 1

Views: 570

Answers (2)

Laughing Vergil
Laughing Vergil

Reputation: 3756

If you absolutely, positively must use BCP, there is one trick that is often used for XML files that should work for JSON files as well.

  1. Do not add a Row terminator value
  2. Make your Field terminator value something that absolutely, positively, cannot exist in the JSON file, such as '\0~\0\0~' (which is NULL + ~ + two NULLs + ~). If this could exist in the JSON, try some other value. Just ensure that it can't exist in the file.

By default, this imports an entire XML file as a unit. It should work on JSON files as well, but I cannot guarantee that.

Upvotes: 0

Laughing Vergil
Laughing Vergil

Reputation: 3756

BCP is not designed for importing a file into a single column, so you run into these problems. To import a file as a single object, use the OPENROWSET(... SINGLE_BLOB) functionality, like this:

INSERT INTO JsonTable(jsonColumn)
SELECT BulkColumn
FROM OPENROWSET (BULK ‘TextFile Path’, SINGLE_BLOB) FileName

Upvotes: 1

Related Questions