SQL Server BCP insert additional columns

Question

My reuirement:

Input File:

1,abc,xyx

2,def,mno

3,ghi,suv

DB Table Structure:

Col1 char

col2 char

col3 char

col4 char

col5 char

Data in Table after BCP:

col1 col2 col3 col4 col5

1 abc xyz ab xy

2 def mno de mn

3 ghi suv gh su

Basically the col4 and col5 are calculated values from col2 and col3 values.

Does SQL Server BCP utility support such kind of operation? Any pointers will be appreciated.

Cheers GT

If col4 and col5 are calculated values from existing columns in the table, why not create the destination table with calculated columns. If you need to index them then set them persisted. You can use BCP as per @edosoft answer below, exporting the data from the source and then importing it. Then you don't need to worry about populating col4 and col5 at all — Davos

edosoft edosoft · Accepted Answer · 2009-05-29T08:19:50

You can use a format file to specify which datafield maps to which column

You can use a format file when importing with bcp:

Create a format file for your table:

 bcp [table_name] format nul -f [format_file_name.fmt] -c -T

This will create a file like this:

 9.0
5
1       SQLCHAR       0       100     ","      1     Col1             SQL_Latin1_General_CP1_CI_AS
2       SQLCHAR       0       100     ","      2     Col2             SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR       0       100     ","      3     Col3           SQL_Latin1_General_CP1_CI_AS
4       SQLCHAR       0       100     ","      3     Col4           SQL_Latin1_General_CP1_CI_AS
5       SQLCHAR       0       100     ","      3     Col5           SQL_Latin1_General_CP1_CI_AS

Edit the import file. The trick is REMOVE the columns you don't need (fields 4 and 5 in this example) AND also update the column count: this is the second row in the format file. In this example, change the 5 to a 3. Then import the data using this format file, specifying your inputfile, this format file and the seperator:

bcp [table_name] in [data_file_name] -t , -f [format_file_name.fmt] -T

SQL Server BCP insert additional columns

5 Answers