BCP utility - line terminator for row

Imagine if we have a table "Names" that has these fields:

ID (int, PK, Identity Seed)

Imagine a CSV having just 2 columns of the 3 above:


how would I use BCP utility to tell it to insert each row where the row has a new line as a terminator for the row to be inserted? The ID in the table needs to be there for obvious reasons and the import file will not have this.

I tried this but it did not quite work. Data does not insert correctly.

bcp Names in C:\DataInput.csv -S . -d TestDB -q -c -t "," -T -r \r

You must create a format file and then use that format file in the bcp.

Format files are usually a royal pain to get working, but once they work, they work very well.


CREATE VIEW bcpNames_View AS SELECT FirstName, LastName FROM Names;


bcp bcpNames_View in C:\DataInput.csv -S . -d TestDB -q -c -t "," -T -r \r

The format file is still a better approach, and it's good knowledge to have in case you get into more complex bulk insert scenarios.

1 Like