SQL Server BCP inserts additional columns
My help:
Input file:
1, ABC, huhi
2, Protection, MnO
3, GHI, suv
Database table structure:
Col1 char
col2 char
col3 char
col4 char
col5 char
Data in the table after BCP:
col1 col2 col3 col4 col5
1 abc xyz ab xy
2 def mno de mn
3 ghi suv gh su
Basically col4 and col5 are computed values from col2 and col3 values.
Does SQL Server BCP support this operation? Any pointers would be appreciated.
Cheers GT
You can use a format file to specify which maps the data fields are in which column
You can use the 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. Trick: DELETE the columns you don't want (fields 4 and 5 in this example) And also update the number of columns: this is the second line in the format file. For this example, change the value 5 to 3. Then import the data using this format file, specifying your input file, this format file, and delimiter:
bcp [table_name] in [data_file_name] -t , -f [format_file_name.fmt] -T
source to share
You can use BCP on a staging table and then insert from the staging table into the appropriate structure in another table.
You can also use BULK INSERT
from within SQL with the same format file and source file as with an external BCP command so that you can run the whole batch in SQL: BULK INSERT
to enter the lookup table and then the INSERT INTO
final table.
Another preprocessing option like Perl (or any other command line tool) is PowerShell to pass data around bits before using BCP (possibly with the XML option): http://www.sqlservercentral.com/articles/powershell/ 65196 /
My preferred option will probably be SSIS, which has the entire arsenal of transformations available to you, including derived columns.
source to share
No, you cannot do this with BCP, although you can use BCP to extract a dataset from a query and dump to a file.
If you want to do this without using a query from source, you will have to bcp to a file, then process the file with a perl script or some of them to create the computed columns, and then re-import the file to the destination with an appropriately formatted BCP control file.
EDIT: BCP is pretty simplistic. If you cannot use client-side tools, you can insert them into a staging table and then compute the derived values in the stored procedure.
source to share
If you're not afraid to do a little bit of programming, you can do it with ADO.NET. This and any other transformations you want to do on the fly can be done quite easily by implementing a custom IDataReader. SqlBulkCopy takes an IDataReader and inserts the inserted data. Then your reader can use the input file and add additional columns, transform data, search for key values, etc.
source to share