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



Answers

If you're not afraid to program a little, you can do this with ADO.NET. This, and any other transformations you wish to make on the fly, can be done quite easily by implementing a custom IDataReader. SqlBulkCopy takes an IDataReader and bulk inserts the data it provides. Your reader can then consume the input file and supply additional columns, transform data, look up key values, and so on.

By : The Dag


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
By : edosoft


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.



This video can help you solving your question :)
By: admin