Search code examples
sqlsql-serverbcp

SQL Server BCP utility - ignore column while importing

I am new to the BCP utility. I want to know if there is a way to exclude a column while doing an import.

Consider this table structure. 

CREATE TABLE [dbo].[test_temp]
(
    [Column1] [varchar](100) NULL,
    [Column2] [varchar](100) NULL,
    [Column3] [varchar](100) NULL,
) 
ON [PRIMARY]
GO

My input file (inputData) has 4 columns:

Column1Value, Column2Value, Column3Value, Column4ValueExclude

When I run my BCP, I would like to exclude fourth column from the input. So that my table gets values from Column1, Column2, Column3. 

bcp "testDB.dbo.test_temp" in  c:\temp\test\inputData.csv -c -t,  -T -S "testDBServer" -e c:\temp\test\error.csv

Is this possible?

Thanks all.

Solution

  • 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.