SQL Server BCP insert additional columns

Question

Hi

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

Answer 1

No, you can't do this with BCP, although you can use BCP to extract a data set from a query and dump to a file.

If you want to do this without using a query from source, you would have to bcp out to a file, post-process the file with a perl script or some such to produce the computed columns and then re-import the file to the destination with a BCP control file of the appropriate format.

EDIT: BCP is fairly simplistic. If you can't use client-side tools you could insert into a staging table and then calculate the derived values in a stored procedure.

Answer 2

You can BCP into a staging table and then insert from the staging table in the appropriate structure to another table.

You can also use the BULK INSERT from within SQL with the same format file and source file as you would from the external BCP command so that you can run the entire batch in SQL: BULK INSERT to table matching input and then INSERT INTO final table.

Another pre-processing option like Perl (or any other command-line tool) is PowerShell to pipe the data around a bit before using BCP (potentially with the XML option): http://www.sqlservercentral.com/articles/powershell/65196/

My preferred option would probably be SSIS, which has the entire arsenal of transforms available to you, including derived columns.

Answer 3

SQL Server Integration Services and transforms would be my approach of choice. Its a pretty simple package to build the transformations required - and SSIS is pretty damn fast, easy to schedule etc.

Answer 4

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