CODEWITHSUNDEEP
sql-serverbcp
Rafael Breno Rocha Reis
Rafael Breno Rocha Reis

Reputation: 27

Insert fixed values using BCP

I'm trying to import a TXT file using bcp.

My TXT file is like this:

abc|cba
xyz|zyx

My Table is like this:

Field_1 -> Identity field
Field_2 -> Varchar(3)
Field_3 -> Varchar(3)
Filed_4 -> Varchar(1) In this case I must set with default value 'P'
Filed_5 -> Varchar(1) In this case I must set with default value 'C'

My table with values must be:

1,abc,cba,P,C
2,xyz,zyx,P,C

Note-> My TXT file is huge (around 200GB), I can't import into another table to then pass all value to this table (just saying).

@@Version-> SQL Server 2014 (SP2)

Upvotes: 0

Views: 779

Answers (1)

jamie
jamie

Reputation: 775

You cannot generate data via BCP, you must depend on SQL Server to do that as Jeroen commented. To add to his comment, the identity value is not a default, you should continue to use the identity property of the column.

For both (identity and default), you must use the -f option to BCP. This the option to include a format file to direct the BCP utility to see and handle the data as stated in the format file.

Using a format file, you can specify which columns in the file are mapped to which columns are in the destination table. To exclude a column, just set its destination value to "0".

The format files and the bcp utility are much larger topics in and of themselves, but to answer your question; yes it is possible and using a format file with modified destination values (set to "0") is the way to do it.

Doing this, you can process the data once. Using powershell to append data is possible, but unnecessary and less efficient. To do this in one action with bcp, you need to use a format file.

Upvotes: 1

Related Questions