BCP utility - line terminator for row

Transact-SQL
1

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

FirstName
LastName
ID (int, PK, Identity Seed)

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

John,Travolta
Adam,Sandler
Kevin,Hart

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

2

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.

3

Alternately:

CREATE VIEW bcpNames_View AS SELECT FirstName, LastName FROM Names;

Then:

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